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):
Blended data like in Tableau
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”)
Table.BlendRows
File with sample: BlendDataTableau.zip
Hi Imke,
Why wouldn’t you recommend using your solution? I come across this problem all the time with scenarios not matching the comparison (i.e. Actuals vs. Budget) and I have tackled the problem using a DAX crossjoin function (as my variance analysis calculations have to be done at a row level, using a SUMX function). But the issue with this is that the ‘click speed’ in power pivot is terrible and can take 30 seconds to calculate. However, I could adopt this ‘table blended’ scenario to my data and it would fix the problem but keen to understand your reasons for not recommending us to use it. Thanks, great work with your posts – always appreciate them!
Hi Dan,
thx for the nice feedback!
First reason is that you’re missing out those time-intelligence-functions in DAX that require a separate date-dimension.
Second reason is that the risk of messing up your reports rise if you take more than your technical key-columns into your reports: If here for example you would like to take the account description as well, you would need to take this field into the selection of key-columns as well in order to show them from the non-matching budget-figures. There will be a chance that they don’t match the descriptions in the actuals (as they are not necessarily covered by a validation-mechanism) and this might lead to inconsistent reports later on.
But if you know what you’re doing this blending-technique can actually save time in the right circumstances. (Therefore the “necessarily” 🙂 )
Cheers, Imke
Normally there is a tiny summation icon next to those column names having whole number as the data type. However, it seems not the case if I take a look at the Fields pane. There is no such icon next to “Amount” of Actuals table and “Amt” of Budget table respectively while the corresponding “Amount” and “Amt” columns of FunctionfnBlend table do have that icons. Could you please explain for me what’s the difference and how to adapt the same logic to the mentioned columns of Actuals table & Budget table?
Sorry Julian, I’m having difficulties understanding your question and requirement.
Would you mind posting your questions in a public forum where you can add pictures of the situations and your requirement and send that link?
Thx, Imke
In addition to my previous reply, I found if I chose “full outer” as the join kind of merge, then the script could be more concise without going for the append step. Am I right? Please comment.
Hi Julian,
as I said, a full outer would be an alternative, but I couldn’t make my mind up how to merge or rename the key columns when there’s no match with the primary table. How did you solve that?
Listed below is the script:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjDUAyIjA0NzJR0lQwMDAzAFIpRiddCkjSDSRmACLm2EptsYTGBKQ3WbgAml2FgA”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Account = _t, Amount = _t, Quantity = _t]),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date”, type date}, {“Amount”, Int64.Type}, {“Quantity”, Int64.Type}}),
#”Renamed Columns” = Table.RenameColumns(#”Changed Type”,{{“Account”, “AccountNo”}}),
#”Merged Queries” = Table.NestedJoin(#”Renamed Columns”,{“Date”, “AccountNo”},Budget,{“Date”, “AccountNo”},”NewColumn”,JoinKind.RightAnti),
#”Expanded NewColumn” = Table.ExpandTableColumn(#”Merged Queries”, “NewColumn”, {“Date”, “AccountNo”, “Amt”, “Qty”}, {“Date.1”, “AccountNo.1”, “Amt”, “Qty”}),
#”Appended Query” = Table.Combine({#”Expanded NewColumn”, Actuals}),
#”Added Conditional Column” = Table.AddColumn(#”Appended Query”, “DateF”, each if [Date] = null then [Date.1] else [Date] ),
#”Added Conditional Column1″ = Table.AddColumn(#”Added Conditional Column”, “AccountF”, each if [Amount] = null then [AccountNo.1] else [Account] ),
#”Removed Columns” = Table.RemoveColumns(#”Added Conditional Column1″,{“Date”, “AccountNo”, “Date.1”, “AccountNo.1”, “Account”}),
#”Renamed Columns1″ = Table.RenameColumns(#”Removed Columns”,{{“DateF”, “Date”}, {“AccountF”, “Account”}}),
#”Merged Queries1″ = Table.NestedJoin(#”Renamed Columns1″,{“Date”, “Account”},Budget,{“Date”, “AccountNo”},”NewColumn”,JoinKind.FullOuter),
#”Expanded NewColumn1″ = Table.ExpandTableColumn(#”Merged Queries1″, “NewColumn”, {“Date”, “Amt”, “Qty”}, {“Date.1”, “Amt.1”, “Qty.1″}),
#”Removed Columns1″ = Table.RemoveColumns(#”Expanded NewColumn1”,{“Amt”, “Qty”, “Date.1″}),
#”Renamed Columns2″ = Table.RenameColumns(#”Removed Columns1”,{{“Amt.1”, “Amt”}, {“Qty.1”, “Qty”}})
in
#”Renamed Columns2″
If possible, would you like to adapt it as a function like you did using “JoinKind.RightAnt”?
Thx Julian, I’ll leave it to the readers to make their own choice.
Guys – for me the simple solution was to
§ append of the two then
§ merge date and account then delete duplicates
§ rejoin both tables using a date/Acct concatenated key
Net – 9 lines of “M” code
Source = Table.Combine({Table1, Table2}),
RemoveOtherCol = Table.SelectColumns(Source,{“Date”, “Account”}),
#”Merge_Date_Acct#” = Table.AddColumn(RemoveOtherCol, “Merged”, each Text.Combine({Text.From([Date], “en-US”), Text.From([Account], “en-US”)}, “”), type text),
RemDups = Table.Distinct(#”Merge_Date_Acct#”, {“Merged”}),
RemMergeCol = Table.RemoveColumns(RemDups,{“Merged”}),
Merge_Actual = Table.NestedJoin(RemMergeCol,{“Date”, “Account”},Table1,{“Date”, “Account”},”NewColumn”,JoinKind.LeftOuter),
Expand_Actual = Table.ExpandTableColumn(Merge_Actual, “NewColumn”, {“Amount”, “Quantity”}, {“Act.Amount”, “Act.Quantity”}),
Merge_Budget = Table.NestedJoin(Expand_Actual,{“Date”, “Account”},Table2,{“Date”, “Account”},”NewColumn”,JoinKind.LeftOuter),
Expand_Budget = Table.ExpandTableColumn(Merge_Budget, “NewColumn”, {“Amt”, “Qty”}, {“Bud.Amt”, “Bud.Qty”})
Thx Drew!
Nice & definitely closer to my net – 6 lines of M-code 😉
BTW: You can skip the merge-step, as you can remove dups when selecting 2 or more columns: So just select Date and Account and remove dups.