Excel PowerQuery Query Name¶
You can find the name and formula of a query
dim qry as workbookquery qryName = qry.name qryFormula = qry.formula qryDesc = "Test description"
... and with those 3 variables, you can create a query!
List All Functions In A Table¶
- Go to: Data > New Query > Blank Query
- Open Advanced Editor
- Type #shared (remove all other code)
- Convert to Table
- Then: can filter in the table by using the normal drop-down at the top of the column
(e.g. "List." to get all the functions applicable on a List)
And if you select one of the items in the table, documentation will appear at the bottom.
Filter on Named Range -
If empty then ALL¶
if param = "" then #"previous row" else Table.SelectRows(#"previous row", each ([column] = param))
Ratios (Group By)¶
- Ratios => group by to a new table "Tbl_Ratios_GroupBy"
- Ratios => Left Outer Join on the common key to get the total next to each line
- Ratios with the total => the division on each line as per your suggestion
- FULL OUTER Join the Data table with the Ratios table
- do the ratio multiplication on each line
- Query Name
- List All Functions In A Table
- Filter on Named Range