“If I have seen further it is by standing on the shoulders of Giants” — Isaac Newton
When talking about M Query, Chris Webb is one of the greatest gurus out there sharing knowledge for the sake of the greater good.
In 2015, Chris Webb wrote the post “Nested Calculations in Power Query,” which I have found super helpful many times; today, I want to share with you two real-life cases where it came very handy.
I will not go into detail about the code, as his original post covers this with great clarity; this post is intended two show two variations of his approach applied to real business cases.
Also, there is a different technique for solving this challenge, described in the “Introduction to Power BI” book by Alberto Ferrari and Marco Russo (page 153) that uses auxiliary tables. I haven’t yet tried it, but I guess that using the MAX/MIN grouping functions, instead of the “Count Rows” and some more transformations and joins, you can get to the desired result.
This project was developed for a company specializing in certifications and tests; for the BI side, we were required to analyze the components of certification tests applied to fabrics.
The challenge was that in the DB provided, each test (one test per row) could contain one or multiple measurements, and each measurement one or various components.
The code used for this transformation does not vary in great length to Chris’ example; the differences are mentioned below:
- Add an Index column for including a unique ID to each test (row.)
- Split column “fiber_content” by delimiter (|)into new rows; each new row is a new Measurement.
- Add a new index; this one is the one to be used in the Rank function.
Once this was done, I could apply the Rank Function having for a result this table:
From there, I only needed two more splits of the “fiber_content” column, the first one by a delimiter (,) into rows to get a row per component, and the final split by delimiter (:) into columns for separating the component name from its percentage.
Tempus is a Project Portfolio Management tool and the data source for this project. After retrieving via its API the milestones of the project, we needed to know which of the milestones was first and last. I had done this with DAX using the RANKX + CALCULATE functions, but I found out that this could also be handled via Power Query.
The variation to the original code is that we needed to rank twice the dates per project, once in ascending order, where the number (1) will always be the first date, and the second in descending order, where the number (1) will always be the last date.
The reason behind having the descending rank is that, as projects can have n number of milestones, the last milestone will never be the same number for all of the projects, and some of our visualizations are required to filter only the last one.
By ranking in descending order, we then know that in this column, the number 1 will always mean the last milestone, no matter if there are one or n number of milestones.
You can find the example files here.
Power Platform Community - Home Page - PowPCo
Welcome to PowPCo, the Power Platform Community that was born on Discord. We are Power Platform enthusiasts sharing fun…