PowerBI - Notes - Part 1

This post contains useful notes for using PowerBI Desktop. I’ve compiled numerous helpful tips for future reference on how to query and transform data.

Some useful methods for data query and transformation

- M code to generate a list of dates in the Query Editor:

S1 - Click following “Blank Query” to create a new Query:

S2 - Click following “Advanced Editor” to bring up the Editor Window:

S3 - Following code would generate the table of list of dates starting from 1 Jan 2023:

Code –>>: 

1
2
3
4
5
6
7
8
// Power Query M code to generate a list of dates from 1st January, 2024,to the current date
let
    StartDate = #date(2024, 1, 1),
    EndDate = Date.From(DateTime.LocalNow()),
    DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"})
in
    DateTable

- Query operation regarding Date and DateTime:

Code –>>:

1
2
// The following ‘M’ code adds a new column based on the existing [Date] column to determine the “Start of Week” date. Note that the second parameter, ‘Day.Monday’, specifies that the week starts on Monday, default is starting on Sunday.
= Table.AddColumn(#"Inserted Day Name", "Start of Week", each Date.StartOfWeek([Date], Day.Monday), type date)

- Small tips to toggle on/off Date Hierarchy:

If you have difficulty to turn off date hierarchy, please do the following:

Right click on the field (in Visualizations/Rows Panel) to uncheck “Data Hierarchy”.