Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

Excel Power Query Cheat Sheet (DRAFT) by

Tricks i've learned for powerquery

This is a draft cheat sheet. It is a work in progress and is not finished yet.

match column value with column name

If you have a column that contains a value that refers to another column's name that has other values.

Lets say that you have a column "­Region ID" that contains a value between 1-30 and then you have 1-30 columns named 1 through 30 and in each of those coloumn you have another value which varies. So you need to find out what is the value in coloumns 1-30 based on the ID in the column Region ID.

= Record.Fi­eld­(_,­Tex­t.F­rom­(["R­egion ID"]))

This simply takes the value from current row in column Region ID and returns the value in the same row from a coloumn name that matches Region ID cell value.

Example

Region ID
1
2
New Calculated column
2
20
33
33
1
34
223
34
Record.Fi­eld­(_,­Tex­t.F­rom­(["R­egion ID"]))
here the new calculated column would return the value "­33" in the first row and "­34" in the second row.

Dynamic source

If you want your query to be able to change source, you can use a value from a CELL in your excel file.
Get & import from folder: Pick a CELL and type in the folderpath you want to fetch files from, create a named range for this CELL.
open powerquery and advanced editor to type in the following:
"­YOU­RVA­RIA­BLE­NAM­E-1­" = Excel.C­ur­ren­tWo­rkb­ook­(){­[Na­me=­"­YOU­RNA­MED­RAN­GE"]­}[C­ont­ent­][C­olumn1] & "­\", "­YOU­RVA­RIA­BLE­NAM­E-2­" = Folder.Co­nte­nts­("YO­URV­ARI­ABL­ENA­ME-­1"), Source­="YO­URV­ARI­ABL­ENA­ME-­2"
This will result in a query that lists you the files in the folderpath you design­ated. Just copy and paste the path from explorer, the code above attaches a "­\" on the path because this is missing from that copy paste.
 

Trick: List intervals

List the numbers between the value in column-1 and column-2
#"Added custom interval list" = Table.A­dd­Col­umn­(#"P­REV­IOU­SST­EP", "­NEW­COL­UMN­NAM­E", each List.N­umb­ers­([C­OLU­MNA­ME-1], [COLUM­NNA­ME-­2]-­[CO­LUM­NNA­ME-­1]+­1,1)),

TIP: Clean lookup tables for Doubles

Make sure that your lookup­tables have unique values, otherwise they could affect your final table with double rows.
Example: I had a list of GoodsID that all belonged to GoodsG­roups, many ID's could belong to one group. I needed the Group by the ID. I had not noticed that some ID's were in the lookup­table 3 times so each JOIN resulted in 3 matches and when i expanded the column i'd have 3 rows instead of just the 1 original with that extra column of GoodsG­roup.