Using advanced operations of Power Query

Power Query is truly a powerful tool for data transformation that helps to enable capabilities that often meet all the needs for modern reporting, analytics, and visual storytelling. While this is true, there may be cases where advanced operations need to take place to meet the business needs. For these cases, Power BI provides additional capabilities that we’ll discuss now.

Using the Advanced Editor

At the heart of Power Query is the data mashup engine. This engine uses the Power Query M language for defining inputs, transformations, and outputs. The M language is a case-sensitive and functional language that is like F# (https://fsharp.org/). The Power Query user interface provides a fast and efficient way of generating Power Query M code that allows developers to be productive. At the same time, the Advanced Editor functionality also allows the generated M code to be viewed and edited if needed. It’s also possible to create a Power BI dataset using Power Query and only write the M code manually, using the Advanced Editor.

Microsoft provides a full reference of the Power Query M formula language at this website: https://docs.microsoft.com/powerquery-m/. The PL-300 exam is not an exam on the Power Query M language, but it is advised to know the basic structure of a Power Query M formula.

Power Query M formulas contain values made up of variables and expressions. These components are contained within a let expression that also uses in. For example, this is a basic method of manually entering three records of sales ID and sales date data into a query:

let sales = Table.FromRecords({

    [SaleID = 1, SaleDate = “2021-10-16”],

    [SaleID = 2, SaleDate = “2021-10-16”],

    [SaleID = 3, SaleDate = “2021-10-17”]})

in  

    sales

Once the data is in the query, it’s possible to transform it using the variety of available functions, such as transforming a text data type to a date data type for the SaleDate column in our data:

let

    sales = Table.FromRecords({

    [SaleID = 1, SaleDate = “2021-10-16”],

    [SaleID = 2, SaleDate = “2021-10-16”],

    [SaleID = 3, SaleDate = “2021-10-17”]}),

    #”Changed Type” = Table.TransformColumnTypes(sales,{{“SaleDate”, type date}})

in

    #”Changed Type”

By adding the line after our data, we can use the TransformColumnTypes function to transform the SaleDate column from a text type to a date type. The value after in also changes as this defines where we are in the stage of transformation. As more transformations get added to the code, in will typically define the latest changes to the data. This enables easy undoing and navigation of the list of transformations within the Power Query window.

Microsoft provides a full reference of the Power Query M functions available here: https://docs.microsoft.com/powerquery-m/power-query-m-function-reference.

To access the Advanced Editor in Power Query, click Advanced Editor under the Advanced section on the View tab of the ribbon.