Forum Discussion
LaurentZ
Helper I
6 years agoCompare 2 tables easily - how do you do ?
Hi, I've 2 tables: - the first contains a forecast on a month (with a breakdown by customers and products) => imported in PQ as "OLD" - the second countains the true realization on the same mon...
Anonymous
6 years agoNot applicable
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
diff
and 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!
LaurentZ
Helper I
6 years agoThank you all for your answers and help.
I was off few days, so let me have a look at all your solutions and test them, then I'll tell you which solution suits me.