Skip to content

Excel PowerQuery

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

  1. Go to: Data > New Query > Blank Query
  2. Open Advanced Editor
  3. Type #shared (remove all other code)
  4. Convert to Table
  5. 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)

  1. Ratios => group by to a new table "Tbl_Ratios_GroupBy"
  2. Ratios => Left Outer Join on the common key to get the total next to each line
  3. Ratios with the total => the division on each line as per your suggestion
  4. FULL OUTER Join the Data table with the Ratios table
  5. do the ratio multiplication on each line

REF: