Automate reports with Power Query M Coding

This blog is for both Power BI and Excel users. In it, we demonstrate how you can automate reports with Power Query M coding

Automate reports with Power Query M Coding
What is Power Query?

The Power Query tool in Excel and the Query Editor in Power BI are identical. They are important tools for both applications. In Excel, many tasks can be automated. Excel users can then work much more efficiently and save a lot of time. This is because Power Query offers tools to structure, clean, and connect live to source data.

To take a query to a higher level of efficiency, a basic understanding of Power Query M (the coding language used in queries) will prove very useful.

How does a query work?

In this article, we will explain an example of date manipulation.

The query editor’s ability to connect live to external data sets is the most important feature of the tool. However, it is important that the query only connects to the data which creates the dashboard/report we need. We should remove all columns which are not needed for the output.

In the query below, a connection has been established to a data set with records going back to 2015. We only need to show the last 4 years of data in the report. To this view, we want the report to show the data starting from last day of the previous month, looking back 48 months.

This can be done manually by using a date filter, but then we must manually change the filter every month. We want to report to update by itself, and this can be achieved with Power Query M coding.

A query is like the macro recorder in Excel. When you perform steps in the query, it writes code. In Excel, the macro recorder writes VBA code, but a query writes Power Query M code. To see the code in the query, we have open the Advanced Editor, which is found on the View tab in the Query Editor.

In the Advanced Editor below, the code shows the only step done with the data so far. Four tables of data have been appended into one table.

Automating Reports with Power Query M

Best practice to keep the Power Query M code simple is to do as much as possible using the tools available in the query editor. Let the query write the code.

In the next example, we will initially set up the date range manually. Later, we will make it dynamicin the Advanced Editor by amending the code.

Each column in the Query Editor has a filter like the filters in Excel. For example, if we have a column with dates, we will have a date filter (see below).

Here, the Between option is used. The first criteria is after or equal to the start date. The second is before or equal to the end date.

Meanwhile in the Advanced Editor, the code for date filtering is now added.

A new line has been added to the code:

#”Filtered Rows” = Table.SelectRows(Source, each [SalesDate] >= #date(2018, 4, 1) and [SalesDate] <= #date(2022, 3, 31))

Now it is time to amend the code to make it dynamic. The Power Query M coding language is logical, however, we will need to change the fixed dates to dynamic dates. The logic here is similar to Date functions in Excel.

Step one

We will change the fixed year 2018 to a dynamic year.

We need to calculate the current year minus 4. The code Date.Year(DateTime.LocalNow())-4 will do the job. ‘Date.Year’ will extract the year part from a date and DateTime.LocalNow is the code to get current date (like the Today or Now function in Excel).

Step two

We will to change the fixed month to a dynamic month.

Date.Month(DateTime.LocalNow()) will do this. Date.Month will extract the month number from a date, and again Local.Now will get the current date.

We will not need to make the day number dynamic, since we always want the data to start from the first day of a month.

Now the start date is dynamic and the report we create will always start from the first day of the month, exactly 48 months back in time. After this, we need to make the end date dynamic.

The logic is the same as for the start date.

Below is the code the query editor wrote when we filtered the dates

#”Filtered Rows” = Table.SelectRows(Source, each [SalesDate] >= #date(2018, 4, 1) and [SalesDate] <= #date(2022, 3, 31))

We should change it to look like this:

#”Filtered Rows” = Table.SelectRows(Source, each [Date] >= #date(Date.Year(DateTime.LocalNow())-4, Date.Month(DateTime.LocalNow()), 1) and

[Date] <#date(Date.Year(DateTime.LocalNow()), Date.Month(DateTime.LocalNow()), 1))

The Result

An amazing thing will now happen to the report we created. Every time we get a new month’s data, the query will remove the oldest month. It will then drag in the previous month to our report so that it again shows the last 48 months.

Conclusion

It may require some knowledge and time to automate reports and dashboards in Power BI or Excel, but it is worth it. You will save double or triple the time you spend learning the techniques to create the reports.

If you want to learn how to automate reports with Power Query M coding, STL can help. We have an Excel Power Query course if you want to learn how to use the query editor in Excel. We also cover the query editor on our Power BI Modelling, Visualisation and Publishing course, if you are a Power BI user.