PQ - Get actual worksheet functions list

[sandy666]

Get actual worksheet functions list


let
Source = Web.Page(Web.Contents("https://support.office.com/en-us/article/Excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188")),
Data0 = Source{0}[Data],
Clean = Table.TransformColumns(Data0,{{"Type and description", Text.Clean, type text}}),
Split = Table.SplitColumn(Clean, "Type and description", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Type and description.1", "Type and description.2"}),
Replace = Table.ReplaceValue(Split," ","#(lf)",Replacer.ReplaceText,{"Type and description.2"}),
Trim = Table.TransformColumns(Replace,{{"Type and description.2", Text.Trim, type text}}),
Replace2 = Table.ReplaceValue(Trim,",","#(lf)",Replacer.ReplaceText,{"Type and description.2"}),
Ren = Table.RenameColumns(Replace2,{{"Type and description.1", "Type"}, {"Type and description.2", "Description"}}),
Index = Table.AddIndexColumn(Ren, "Index", 1, 1),
Reorder = Table.ReorderColumns(Index,{"Index", "Function name", "Type", "Description"})
in
Reorder


part of data:


IndexFunction nameTypeDescription


1ABS functionMath and trigonometryReturns the absolute value of a number


2ACCRINT functionFinancialReturns the accrued interest for a security that pays periodic interest


3ACCRINTM functionFinancialReturns the accrued interest for a security that pays interest at maturity


4ACOS functionMath and trigonometryReturns the arccosine of a number


5ACOSH functionMath and trigonometryReturns the inverse hyperbolic cosine of a number


6ACOT functionMath and trigonometryReturns the arccotangent of a number


7ACOTH functionMath and trigonometryReturns the hyperbolic arccotangent of a number


8AGGREGATE functionMath and trigonometryReturns an aggregate in a list or database


9ADDRESS functionLookup and referenceReturns a reference as text to a single cell in a worksheet


10AMORDEGRC functionFinancialReturns the depreciation for each accounting period by using a depreciation coefficient


11AMORLINC functionFinancialReturns the depreciation for each accounting period


12AND functionLogicalReturns TRUE if all of its arguments are TRUE


13ARABIC functionMath and trigonometryConverts a Roman number to Arabic
as a number


14AREAS functionLookup and referenceReturns the number of areas in a reference


and so on ...