Short Power Query Tips
How to compare null dates?
[#”Change Date (FT)”] = null
[#”Change Date (FT)”] <> null
How to Concatenate Text and Numeric fields?
Use & and Text.From function:-
if
[Report Year] = [Year] and [Month] = 1
then “Jan “ & Text.From([Year])
else
“Dec “ & Text.From([Report Year])
How to quickly add new Step?
Click on fx Button in Formula Bar on Top.
How to combine rows in to comma separated values in column?
How to get maximum date from a date field?
Add custom column
#”Removed Duplicates”[Entry Date] — this will create a list
Add List.Max to it to get max Value.
List.Max(#”Removed Duplicates”[Entry Date])
How to get years between between two dates?
Get Abpgupta’s stories in your inbox
Join Medium for free to get updates from this writer.
Duration.Days([Date1] — [Date2])
You can use Number.Round function to round numbers.
Date.FromText can be used to convert Text to Date and compare for dummy dates.
Final calculation in Power Query:-
“Years to Complete”, each if [Final Date] = Date.FromText(“01/01/1901”) then -9999 else Number.Round(Duration.Days( [Final Date] — [Admission Date])/365)
How to filter rows in a table based on other table?
You can use Merge Query in Target query and have inner join with Source Table.
Finally, you can remove source table column from resultant step to have filtered Target Table without need to add any columns from Source.
Power Query Naming Conventions
It’s better to do rename columns as early as possible in the model to avoid any issue in subsequent transformation steps when name is changed.
Option to move Key columns to top of the table in Data Model View
In Layout, when nothing is selected, in Property Pane, highlight option gives move join columns to top of the table to make it easy to see them.
If you select a table and define Key Column, it can help in identifying which column contains unique values. If PBI doesn’t allow it for the column, it means we need to correct it in Power Query editor first.
How to create sorting for each group?
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
To be continued …
