Power Query: Taking (Away) Steps for Efficiency

Welcome to our latest Power Query blog.  Last time we looked at M language syntax in a simple query.  Today I add some code to operate on each row in a table and show how bulky code can be simplified.

Using the ACCT_Order_Charges_with_Group query I edited last time, I will demonstrate how M language can be configured to operate on each row in a table.   To do this, I will add a column that is a product of two of my numerical columns, so I choose Amount and Order_Line_Number.

Having selected my two columns, in the ‘Add Column’ tab in the ‘Standard’ section I choose to add a column that multiplies my chosen columns:

When I click on ‘Multiply’, a new column called ‘Inserted Multiplication’ is added to my table (which I think is an improvement on the default name ‘Multiply’ used in previous versions of Power Query, though I think it would be more useful to default to ‘Column1 x Column2’!).  The formula for this step appears in the window at the top of the editor:

Readers of my last blog will notice the ‘#’ which tells Power Query to ignore the spaces in the column names.  The other important part of this formula is ‘each List.Product’, which is what tells Power Query to apply the multiplication to each row in the table.

As I have already noted, the column name is not particularly user friendly.  I could rename the column using the buttons, adding another step, but instead I choose to avoid adding another step by editing the formula associated with adding the new column:

Clicking the tick icon executes my change (as will leaving the change in place and clicking anywhere in the table), and the column is added with my chosen name in one step.

I can also add a step and then choose to combine it with another step by substituting the whole formula for that step into the next line.  This works well if the step being inserted has a short amount of M language associated with it; too much substitution can make code hard to follow and therefore of less value to share with other users.

I will make a simple change by amending the data type on my newly renamed column Order_Line_Number x Amount from ‘Currency’ to ‘Whole Number’.  On the ‘Transform’ tab, I select my column and then in the ‘Data Type’ section I choose ‘Whole Number’ from the dropdown:

I choose to add this step and then open the ‘Advanced Editor’ from the ‘Home’ tab:

The two lines before the ‘in’ statement are:

At first glance, these may look complicated, but notice that the first line sets the added column to ‘Currency.Type’. The second line changes this to ‘Int64.Type’.

So, if I set the type to ‘Int64’ when the column is created, I don’t need the second ‘Changed Type’ line. I can change the type and delete the step. This is not the end of the story though – last time out I outlined some of the syntax rules, and one of them is that each step must refer to the previous step, and another is that the last line before ‘in’ should not have a comma after it. Therefore, by removing a step manually in the editor I break the sequence; I need to remedy this and remove the extra comma:

The 'in' statement currently refers to the line I have removed:

I need to change this to refer to what is now the last line above it:

Having done this and removed the comma before ‘in’, I save my changes and look at the table:

The step to change type has disappeared, and the type on my column is now ‘Whole Number’, as shown next to the ‘Data Type’ section.  I have successfully simplified the code behind the query.

Next time I will be looking at a rather more specific topic - how to set up running totals…

Want to read more about Power Query? A complete list of all our Power Query blogs can befound here.  Come back next time for more ways to use Power Query!