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:

  • Query Name
    • https://gallery.technet.microsoft.com/office/VBA-to-automate-Power-956a52d1#content
    • https://stackoverflow.com/questions/36691352/change-the-text-of-power-query-using-vba
  • List All Functions In A Table
    • http://radacad.com/power-query-library-of-functions-shared-keyword
  • Filter on Named Range
    • https://stackoverflow.com/questions/38507795/power-query-filter-by-all-when-the-param-is-empty