Getting Started with M in Power Query

If you went bonkers the first time you opened the Advanced Editor in Power Query, don’t worry that makes the two of us.

In this post, I’d help you understand the construct and the fundamentals of the M Language in Power Query so that you are not scared the next time you open Advanced Editor.

 

Getting Started with M Language in Power Query – Video

 

We’ll talk about 3 important aspects in M Language – The Syntax, An Example Query and Power Query Objects (like, tables, lists, records etc..)

 

#1 Power Query M Language Syntax 

I am working with a simple 3 step query

The query looks like this in the Advanced Editor.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [Age]>=18),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows", {"Age"})
in
    #"Removed Columns"

Observe that every step in power query acts as a feeder to the next step.

 

• Learning 1 – Every Step in Power Query is like a Variable
  • These variables store the output of that step. This output could be a table, list, function, value etc. which you can then call in any other step of the query.
  • If the Step Names contain spaces, they should be placed in double quotes preceded by a hash (#) symbol as shown. See the step #”Filtered Rows”.
• Learning 2 – All steps end with a comma (,) to mark it’s closure except the last step.
• Learning 3 – let and in are the two keywords that allow you to define the steps in the query. They are very similar to VAR and RETURN keywords in DAX.
• Learning 4 – Steps in Power Query need not follow a coherent order. Meaning, even if we jumble the steps in the query the output will not change. For example, I have jumbled the steps of our initial query.

let
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows", {"Age"}),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [Age]>=18)
in
    #"Removed Columns"

The output still remains the same

• Learning 5 – The Evaluation of the Query starts BOTTOM UP not Top Down.

1. • Power query will start evaluating the query from the last step – #”Removed Columns”.
   • To evaluate #”Removed Columns”, it first needs to evaluate #”Filtered Rows”.
   • And to evaluate #”Filtered Rows”, the query needs to evaluate Source.
   • So even if the query is not following a coherent order, the bottom up evaluation (also known as the lazy evaluation) will evaluate each step required to render the output.

 

#2 An Example Query

Let’s create a simple 3 step query that counts the elements of the list and multiplies the number by an arbitrary number.

let
    mylist = {"A", "B", 1},
    listcount = List.Count(mylist),
    multiply = listcount * 2
in
    multiply

Here is how the 3 steps work out.

1. We define a list and assign it to the variable mylist. A list is defined within curly brackets {}.
2. List.Count then counts the elements in the list.
3. We then multiply the listcount by 2 to return the output as 6.

 

#3 Objects in M Language – Tables, Lists, Records, Scalars and Functions

Tables – are 2 dimensional objects in power query that ideally have multiple columns and rows. Tables can be defined manually using the #table function in Power Query.

 

Lists – are one columnar data structures. Note – A single columnar table is NOT a List.

To create a List manually, use the curly brackets {}. A simple list of 3 numbers looks like this!

To extract a list (i.e. a column) from a table, We write the name of the column in square bracket preceded by the table name.

ExtractList = TableName[ColumnName]

 

Records – is a single row of the data. It contains the column headers and row value.. To create a record of a data we use the following syntax.

CreateRecord = [columnname1 = value1, columnname2 = value2]

For example:

To extract a record from a table we can use syntax similar to the list

ExtractRecord = TableName{rowindexnumber}

1. Please note that Power Query starts the counting from 0 and not 1.
2. Even though the output looks columnar, its actually a record with column headers and values.

 

Scalar Values – are single values. It could be of any data type (integer, text, etc.)

To manually create a scalar value you can simply write an integer or a text in the formula bar. A text value has to be enclosed in double quotes – “Apple”

The syntax to extract a value from a table would look like this.

ExtractScalar = TableName[ColumnName]{RecordNumber}

The following will extract the 3rd row from the Age Column.

 

Functions – are built-in formulas that you can use to transform tables, lists, records or single values in Power Query.

To view all the functions in power query you can either go to Microsoft Documentation or type #shared as a new step in the formula bar.

= #shared

 

While learning functions – focus on the Input parameters and the Output of functions

For example, if we are to count the number of rows of our input table, we would use

Table.RowCount(Source)

1. This function takes a table as an input argument (Source in our case) and returns a scalar value as output.
2. Therefore, it’s important to be vigilant about the input and output data types.

 

More on Power Query:

• Variables in DAX
• 5 Tricks to Reduce Steps in Power Query
• Dynamic Unpivoting in Power Query
• Clean Trial Balance Data | Power Query Challenge
• Dynamic Data Types in Power