Category: Microsoft

Categories
Application Data Visualisation Excel Training Microsoft Microsoft Office Power BI

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.

Categories
Hints & Tips Power Apps Power BI Technology

Power BI DAX – the mysterious Calculate function 4 – tricks

DAX (Data Analysis eXpressions) is the function language in Power BI desktop. When DAX is used to create measures, it can hard to understand the logic at times. Especially one piece, the Calculate function, can be challenging.

 

This is the fourth part of a series of blog posts, which will investigate the mysterious Calculate function.

The Calculate function react differently to row, column, filter, and table context than the other DAX functions and can be used with date intelligence and can handle many other special requirements.

 

Trick the data model to Pass Filters from Many to One Side of Relationship

In this blog post you will see how the Calculate function can trick the data model to Pass Filters from Many to One Side of Relationship.

In a report the number of cities you have sold to need to be visualised.

A DistinctCount function can count distinct entries. However, as you can see below, the DistinctCount function cannot filter the result by month. It just returns 49, which are the total number of cities sold to.

 

 

The reason that the DistinctCount function cannot filter it by month is clear in the data model.

 

It goes against the filter direction in the relationship between the Orders and the Customers table.

The Dates table is related to the Orders table, and the filter direction is going from the Dates table to the Orders table, but the filter direction in the relationship between Orders to the Customers table is going from the Customer table to the Orders table. This means that the Dates table cannot filter the cities.

Distinct Shipping Methods

The DistinctCount function can show the number of distinct shipping methods used each month. This is because the relationship’s filter direction between the Dates and Orders table is going from the Dates table to the Orders table.

The task was to count how many different cities sold to each month. So how are you going to achieve this?

The mysterious Calculate function can handle this, as it can handle a lot of other special tasks.

The DAX measure will look like this:

Distinct Cities = CALCULATE(DISTINCTCOUNT(Customers[City]),orders)

By nesting the DistinctCount function inside the Calculate function and adding the Orders table to a filter argument in the Calculate function, you now can pass filters from Many to One side of Relationship (go against the filter direction). You will need to enter the Calculate function into the expression to reference the table you want the expression from as well as in the filter argument the table, which are related to the table in the expression.

You can see above that now the measure returns the right result.

Distinct Products Sold

If you need to visualise the number of distinct products sold each month, you can use a similar approach.

Again, you will have to go against the filter direction (Pass Filters from Many to One Side of Relationship). Here the relationship between the Items table and the Line Items table.

Conclusion

I call the Calculate function the mother of all DAX functions. It is the most important DAX function (my personal opinion). But you need to understand how it reacts to row, column, filter, and table context, and can be used to handle different issues. As a result, you will get the best out of it.  Concluding in this blog which has shown how it can pass filters from Many to One Side of Relationship.

This is part 4 of a series of blog posts as mentioned at the top about the mysterious Calculate function in Power BI Dax.