Guest Post: Using List.Accumulate for Input/Output Genealogy

Foreword from Imke:
Pat Mahoney is a very active fellow super user in the Power BI community and has a very strong interest in all things Power Query. He also has a YouTube-channel with interesting videos about Power BI. Here he is sharing a nifty solution for a specific kind or parent-child-challenge which he solves with the List.Accumulate-function:

The need to know which inputs are related to which outputs (and vice versa) is a common business use case, whether it’s lots used in a multi-step manufacturing process, reporting relationships in an organization, or other scenarios. As organizational relationships are usually Many:1 (employees:supervisor), in Power BI a string showing the org hierarchy can be generated using the PATH function in DAX. However, supply chains (and many other scenarios) frequently have M:M relationships and a different solution is required. In this article, an M function that leverages List.Accumulate is described that generates such relationships from an input table that has input/output columns. This is not the first function/code to do this of course (see one of Imke’s posts on this subject), but the one shown here uses a different approach, gives an option of outputs, and provides another tool for the toolbox.
Read more

Exception reporting and management for missing fuzzy matches in Power BI

In a previous article I have described how to use Fuzzy merge to match misspelled data to valid categories. With the sample data at hand, all values could be matched to existing categories. But what can you do if there are some entries that cannot be matched to existing categories?

I would create an “unmatched” value to the categories table before loading to the data model and assign the “unmatched”-value to all unmatched entries. Then I would create a measure that counts the entries within the unmatched category. I would then create a data driven alert, based on this measure. That would trigger an e-mail to the person responsible for maintaining the list. Or, if responsibilities are a bit more complex, trigger a flow in Power Automate that could handle certain conditions to be followed as well.

Let’s look at some details:

Add an “unmatched” value to the categories table for exception reporting

First I select the category table and deselect “Enable load”, as I want to use this table only for the match. The table that shall be loaded to the model will contain the value for the unmatched items as well. Therefor I reference this query and create a new query. There I add a new row with the following formula:

Categories & #table({"Column1"}, {{"Unmatched"}})

Exception reporting for fuzzy matching

 

Replace unmatched values

Read more

Split up an existing Power BI report into a Golden Dataset and a thin report

The other day I discovered a neat way to split up an existing Power BI report into a Golden Dataset and a thin report file with very few adjustments to the existing setup. Imagine you have a Power BI report published for some time already in an app with row level security. Now, you want to create other reports from the dataset as well and decide it’s time to create a golden dataset from which multiple other thin reports can also be fed from as well. But ideally you want to keep your published app, that many users are working with already, unchanged.

Concept to split up existing Power BI Report to Golden Dataset

Simply follow these steps in meticulous order and the world is yours 🙂

Steps to split up existing Report to Golden Dataset

Read more

Performance tip for aggregations after joins in Power Query and Power BI

This article was edited on 2nd Nov 2019. JoinKind.Local has been removed to avoid problems with merges on tables with primary keys:

In this article you’ll learn how to speed up the aggregation of joined/merged tables by orders of magnitude (I recorded up to 30 times faster execution times). This method works for merges where both table have multiple rows for each keys. If one of your tables has a primary key, the method Chris Webb describes here works just as good: Chris Webb’s article on how to improve performance on aggregations after joins using primary keys .

You can follow along the different methods in this file:  PerformanceAggregationsAfterMerges1_Upload.zip

Background

When you join a table to another table in Power Query, the UI gives you the option to either expand the columns (default) or aggregate the contents of the joint tables. That’s useful if multiple rows are returned for the rows of the table that has been joined to (left table):

Read more

Query folding on JoinKind.Inner gotcha for Power BI and Power Query

If you query databases who support query folding, you’re probably very aware of every step you take and check if folding happens with every new step like so:

“View Native Query” shows folding query that’s send back to the server (if not greyed out)

Folding will (usually) happen as long as “View Native Query” isn’t greyed out.

So when doing an inner join on tables whithin the same database, I was a bit surprised to see this greyed out actually. As according to the literature, it should fold.

But guess what? After I expanded a column from it, the folding was back again: Read more

How to get more out of your Graph API custom connector in Power BI

The Graph API can deliver a huge amount of interesting data from your Microsoft 365-universe, but the Graph API custom connector for Power BI is not able to retrieve everything from it in its current shape. So I’ve modified it a bit to squeeze out a bit more of its sweet juice.

Problem

When trying to get the details for planner tasks, the following error-message appears:

Error in Graph API custom connector when retrieving details from planner tasks

Solution

Read more