Today I came across a question in the PowerBI-forum if blending data was possible in Power BI like in Tableau. Although I wouldn’t necessarily recommend it, it’s definitely is a nice challenge. So the following function will interlace the rows from 2 tables like the blending-function in Tableau does. Just that we cannot use any aggregators on the attributes and are not able to use measures, as this takes place in the query-editor.
In our example we have a table with actual figures and one with budget figures:
We want to add 2 columns from the budget table to the actual table: “Amt” and “Qty” (red). Where there’s no match of budget – figures with actuals, there need to be added rows which hold only values from the budget figures (yellow):
So we could do a join in full-outer-mode, but then we would need to find a way to put the date- and AccountNo-values into the existing columns of the actual figures. Instead we will identify those rows who need to go below the actuals and then do a join in left-outer mode just to add the values of the 2 new columns.
You need to feed this function the following parameters:
- Name of the primary table (“Actuals”)
- Name of the secondary table (“Budget”)
- Key column names of the primary table (“Date”, “Account”)
- Key column names of the secondary table (“Date”, “AccountNo”)
- Column names for the value columns (“Amt”, “Qty”)
File with sample: BlendDataTableau.zip