Forum Discussion
Helper ICompare 2 tables easily - how do you do ?
Community ChampionHello LaurentZ
here another approach by using Table.Join, expanding the result, making your calculation and reshaping it.
After that join all 3 created tables
let
New = #table
(
{"Scenario","Customer","Product","Quantity"},
{
{"Actuals","A","1","5"}, {"Actuals","B","1","5"}, {"Actuals","B","2","1"}
}
),
Old = #table
(
{"Scenario","Customer","Product","Quantity"},
{
{"Forecast","A","1","3"}, {"Forecast","B","2","2"}
}
),
Difference =
let
ToJoin = Table.NestedJoin(New, {"Customer", "Product"}, Old, {"Customer", "Product"}, "Old", JoinKind.FullOuter ),
#"Expanded Old" = Table.ExpandTableColumn(ToJoin, "Old", {"Scenario", "Customer", "Product", "Quantity"}, {"Old.Scenario", "Old.Customer", "Old.Product", "Old.Quantity"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Old",null,"0",Replacer.ReplaceValue,Table.ColumnNames(#"Expanded Old")),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Number.From([Quantity])-Number.From([Old.Quantity])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each "Difference"),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom.1", "Custom", "Customer", "Product"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Custom", "Quantity"}, {"Custom.1", "Scenario"}})
in
#"Renamed Columns",
Append = New & Old & Difference,
#"Changed Type" = Table.TransformColumnTypes(Append,{{"Quantity", Int64.Type}})
in
#"Changed Type"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Helper IDear Jimmy801 , v-alq-msft , ziying35 , Anonymous , Greg_Deckler ,
Thank you all for your answer.
I saw some solutions but all are working with small tables and limited volume of data.
So let me share a table (excel) with totally fake data (figures are generated with RANDBETWEEN function).
OneDrive - Excel File (edit I also put a pbix file, but I need an output in Excel, not in PowerBI)
So there are 2 tabs: NEW and OLD with Customer / Product in common, and up to 12 "Value Drivers", already imported in PowerQuery.
The aim here is to create a table corresponding to NEW - OLD, by keeping the same level of data (customer/product/all value drivers), named DIF
And at the end, append NEW, OLD & DIF and play with the Status to display the data I want (Actuals, Forecast, Difference) - no problem here I know how to do it.
Until now I tried this:
Take OLD, multiply all Value Column * -1, then append NEW and OLD-1 and then GroupBy to only kepp the difference... very long.
I'm using this workaround but I would really like to get a better way, more flexible if you know how to proceed.
Thank you.
Laurent
I had not given code of any kind, I had limited myself to sharing some reflections (of common sense) on the dimensional analysis of the problem.
One of those considerations (*) I have roughly put it into practice on the tables you provided, just to get an idea of the times.
It seems to me that everything you ask for can be done within minutes.
I attach a link to the pbix file.(*) the one that suggests the Roman dictum "divide et impera!"
- ImkeF
empty post
I got the following result in less than 1':
you have to group by Customer field both table NEW and OLD:
then produce the difference Table:
let Source = Table.NestedJoin(OLD, {"Customer"}, NEW, {"Customer"}, "NEW", JoinKind.LeftOuter), #"Expanded NEW" = Table.ExpandTableColumn(Source, "NEW", {"cust"}, {"NEW.cust"}), #"Added Custom" = Table.AddColumn(#"Expanded NEW", "diff", each diff([NEW.cust],[cust])), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"diff"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"diff", "cust"}}) in #"Renamed Columns"which use this function:
let diff = (old,new)=> let names=Table.ColumnNames(old), mm=Table.TransformRows(old, (rowOld)=> Record.FromList({"Difference",rowOld[Customer],rowOld[Product]}&List.Transform({3..20}, each let rowNew=new{[Product=rowOld[Product]]}? in Record.FieldValues(rowOld){_}-Record.FieldValues(rowNew){_}),names)) in Table.FromRecords(mm) in diffand finally put all together:
let Source = Table.Combine({OLD[[cust]], NEW[[cust]], Difference}), #"Expanded cust" = Table.ExpandTableColumn(Source, "cust", {"Scenario", "Customer", "Product", "Val Driv 1", "Val Driv 2", "Val Driv 3", "Val Driv 4", "Val Driv 5", "Val Driv 6", "Val Driv 7", "Val Driv 8", "Val Driv 9", "Val Driv 10", "Val Driv 11", "Val Driv 12", "Val Driv 13", "Val Driv 14", "Val Driv 15", "Val Driv 16", "Val Driv 17", "Val Driv 18"}, {"Scenario", "Customer", "Product", "Val Driv 1", "Val Driv 2", "Val Driv 3", "Val Driv 4", "Val Driv 5", "Val Driv 6", "Val Driv 7", "Val Driv 8", "Val Driv 9", "Val Driv 10", "Val Driv 11", "Val Driv 12", "Val Driv 13", "Val Driv 14", "Val Driv 15", "Val Driv 16", "Val Driv 17", "Val Driv 18"}) in #"Expanded cust"PS
you could find usefull place some if.. the .. else in the function to check case where new and old doesn't match!
