Power Query - Tips and Tricks

                                                                                                                 Check list of all posts

The document lists some tips and tricks I have experienced in my past projects, besides some advanced solutions, such as  How to calculate running totals by group in Power Query; What to do when the Power query editor and refresh is so slow with remote files? ;How to filter for the most recent version of each day in Power query, like SQL: ROW_NUMBER() OVER(PARTITION BY ORDER BY)

1. Turn query into function with 3 steps: 

1) Add parameter; 2) Add parameter to script with Advanced editor; 3) right-click to turn query to a function.

2. Add parameter ParameterX to column simple use it : Table.AddColumn(#"stepX", "ColumnName", each ParameterX )

3. Add csv table as new column:  

    #"Extract file" = Table.AddColumn(Source, "File", each Csv.Document([Content],[Delimiter=",",  Encoding=1252, QuoteStyle=QuoteStyle.None])),

4. Add table as new column, instead of using cross join

    #"Added TableX" = Table.AddColumn(Source, "TableX", each #"TableX"),

5. Extract StartDate and EndDate as List.Min(DateRange[Date])

6. Get a REFRESH DATE to Power BI report

let Source = DateTime.LocalNow() - #duration(0,4,0,0), 

#"Converted to Table" = #table(1, {{Source}}), 

#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Datetime"}}) 

in #"Renamed Columns"

7.  Custom aggregation: Get group, then change to below, such as get the first row

      {"ColumnXOfTop1Row", each List.First([ColumnX]), type number}, 

8.  Change column sequence

    #"Removed Other Columns" = Table.SelectColumns(#"StepX",{"Column1", "Column2", "Column3", "Column4", "Column5"}),

9.  Sort multiple columns

Table.Sort(#"StepX",{{"Column1", Order.Ascending},{"Column2", Order.Ascending},{"Column3", Order.Ascending}})

10. Dynamic SQL with parameter

((0 = "& SQLFilter1 &") OR (Division_ID = "& SQLFilter1 &"))

11.  Conditional filter:

  #"Filtered Rows" = Table.SelectRows(#"Inserted BatchID", each (((RunID=null) and (1=1) ) or ((RunID <>null) and ([BatchID] = RunID)))),

12. Conditional source:

    Source = if ParameterX = "DEV" then Sql.Database("DEV", "Database") else Sql.Database("TEST", "Database") ,

if SampleDataOnly = "YES" then 

            Sql.Database("SQL server name", "Database name", [Query="select TOP 10 ...."])

        else 

            Sql.Database("SQL server name", "Database name", [Query="select ...."])

13. Replace multiple values:

Table.ReplaceValue(#"StepX",null,0,Replacer.ReplaceValue,{"Column1", "Column2"})

14. Data mapping with multiple columns:

Table.RenameColumns(#"StepX",{{"OldColumnName1", "NewColumnName1"}, {"OldColumnName2", "NewColumnName2"}, {"OldColumnName3", "NewColumnName3"}})

15. Replace ColumnA with other ColumnB if columnA is null

(ref. https://gorilla.bi/power-query/replace-values/)

= Table.ReplaceValue(

     #"Step X", 

     each [ColumnA], 

     each if [ColumnA] = null then [ColumnB] else [ColumnA] ,

     Replacer.ReplaceValue,{"ColumnA"}

 )

16. Calculate multiple columns:

= Table.TransformColumns(#"StepX", {{"Column1", each _ / 10, type number}, {"Column2", each _ / 10, type number}})

17. Create table

let

    Source = #table

(

    type table

        [

            #"Source" = text,        

            #"Target" = text

        ],

    {

        {"AUDIO RECORDER","Misc"},

        {"CAMERA","Misc"},

    ....        {"INTERNAL CARD SLOT","Card Reader"}

     }    

)

in

    Source

18. Incremental refresh with native query

**RangeStart:

#datetime(2022, 1, 1, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true]

RangeEnd:

#datetime(2022, 6, 1, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true]

DateKey:

let

    Source = (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)

in

    Source

let

    StartDateKey = DateKey ( RangeStart),

    EndDateKey = DateKey ( RangeEnd),

    SQLstmt = "select MASTER, YYYYMMDD, Value  from dbo.CYTEST1 where YYYYMMDD > " & Text.From (StartDateKey) & " and YYYYMMDD <= " & Text.From (EndDateKey),

    Source = Sql.Database("database server", "database", [Query=SQLstmt])

in

    Source

18. use List to dynamically handle any M  for
Static:
Table.RenameColumns(#"StepX",{{"OldColumnName1", "NewColumnName1"}, {"OldColumnName2", "NewColumnName2"}, {"OldColumnName3", "NewColumnName3"}})

Dynamic: 
Table.RenameColumns(#"StepX",ListMap)

where ListMap = Table.ToRows( "TableX")

• OldColumnName1 NewColumnName1 
• OldColumnName2 NewColumnName2 
• OldColumnName3 NewColumnName3