Comparing 2 merged queries in Power Query

[kevdragon1]

Hi all,

I have a report that I download every month from a CRM and need to compare it with the report from the previous month.

What I did is I loaded the CURRENT month report in a PQ as a connection and did the same for the PREVIOUS month report. Then I created a Merge Query with a left outer join to compare the first column which is a key used in by the CRM to detect changes. The result is something like this :

Key	Location	Royalty	Old.Key	Old.Location	Old.Royalty
xy123	US	4%	xy123	US	5%
tre456	US	4%	tre456	null	4%
fgew532	Canada	4%	fgew532	null	4%

My goal is to find out what changed from each column. So what I then did was to create a custom column for each column of the merged table. So I create a custom column for location : Location = Old.Location and Royalty=.Old Royalty :

Key	Compare Location	Compare Royalty
xy123	TRUE	FALSE
tre456	FALSE	TRUE
fgew532	FALSE	TRUE

Here are my questions :

1. Am I doing this right? I have no idea if this is the best/most efficient way to do it.
2. Is there a faster way than creating the comparing columns 1 by 1? Because my real reports have 50+ columns and I don't feel like creating the compare columns 1 by 1.
3. Finally, is there a way, once all the comparing has been done, to return the name of the columns that are FALSE for each row? For example, I would like a dashboard where I can see, at a quick glance, that the key fgew532 has 1 FALSE in the Location column. Since I have more than 50 columns it is hard for me to go and check all the time what column was FALSE for each row (because of the merge, I have more than 100 columns in the merged table). When the result is TRUE for a column I want to ignore that column. I only care about the columns with a FALSE.
   
   Thanks

 

[alansidman]

Book7
	A	B	C	D	E	F	G	H	I
1	Key	Location	Royalty	Other		Key	Location	Royalty	Other
2	xy123	US	4%	5		xy123	US	5%	5
3	tre456	US	4%	6		tre456	US	4%	7
4	tgew	Canada	4%	5		tgew	Canada	4%	5
5
6
7	Key	Value	Table2.Value	Location	Royalty	Other
8	tgew	0.04	0.04		True
9	tgew	5	5			True
10	tgew	Canada	Canada	True
11	tre456	0.04	0.04		True
12	tre456	6	7			False
13	tre456	US	US	True
14	xy123	0.04	0.05		False
15	xy123	5	5			True
16	xy123	US	US	True
Sheet1

I loaded each table to PQ and unpivoted the data. Highlight first column and unpivot other columns
Then merged two tables on first two tables

let
    Source = Table.NestedJoin(Table1, {"Key", "Attribute"}, Table2, {"Key", "Attribute"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Value"}, {"Table2.Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Custom", each if[Table2.Value]=[Value] then "True" else "False"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Attribute]), "Attribute", "Custom")
in
    #"Pivoted Column"

 

[kevdragon1]

Book7
	A	B	C	D	E	F	G	H	I
1	Key	Location	Royalty	Other		Key	Location	Royalty	Other
2	xy123	US	4%	5		xy123	US	5%	5
3	tre456	US	4%	6		tre456	US	4%	7
4	tgew	Canada	4%	5		tgew	Canada	4%	5
5
6
7	Key	Value	Table2.Value	Location	Royalty	Other
8	tgew	0.04	0.04		True
9	tgew	5	5			True
10	tgew	Canada	Canada	True
11	tre456	0.04	0.04		True
12	tre456	6	7			False
13	tre456	US	US	True
14	xy123	0.04	0.05		False
15	xy123	5	5			True
16	xy123	US	US	True
Sheet1

I loaded each table to PQ and unpivoted the data. Highlight first column and unpivot other columns
Then merged two tables on first two tables

let
    Source = Table.NestedJoin(Table1, {"Key", "Attribute"}, Table2, {"Key", "Attribute"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Value"}, {"Table2.Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Custom", each if[Table2.Value]=[Value] then "True" else "False"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Attribute]), "Attribute", "Custom")
in
    #"Pivoted Column"

Thanks for the response. This doesn't solve my problem because the result has the key in multiple rows. Any way to make it easier for the eye to see quickly what the difference is between old and new?

 

[alansidman]

Maybe:

let
    Source = Table.NestedJoin(Table1, {"Key", "Attribute"}, Table2, {"Key", "Attribute"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Value"}, {"Table2.Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Custom", each if[Table2.Value]=[Value] then "True" else "False"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Attribute]), "Attribute", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Value", "Table2.Value"}),
    #"Filled Up" = Table.FillUp(#"Removed Columns",{"Location", "Other"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Royalty] <> null))
in
    #"Filtered Rows"

 

[JGordon11]

Here's a function (I called it Table_Compare) to compare two tables that have matching headers and matching first columns:

(table1 as table, table2 as table) as any =>
let 
    tcn1 = Table.ColumnNames(table1),
    tcn2 = Table.ColumnNames(table2),
    //Check if headers and key (first) columns match
    Output = if (tcn1=tcn2) and (Table.Column(table1,tcn1{0}) = Table.Column(table2,tcn2{0})) then
                let 
                    List_Compare = (list1 as list, list2 as list) as list => 
                        List.Generate(()=>0,(x)=> (x)<List.Count(list1), (x) => x+1, (x)=> list1{x} = list2{x}),
                    ttr1 = Table.ToRows(Table.RemoveColumns(table1,tcn1{0})),
                    ttr2 = Table.ToRows(Table.RemoveColumns(table2,tcn2{0})),
                    ct = Table.RowCount(table1),
                    NewRows = List.Generate(()=>0, (x)=>x<ct,(x)=>x+1, (x)=> {Record.Field(table1{x},tcn1{0})} & List_Compare(ttr1{x}, ttr2{x})),
                    Result = Table.FromRows(NewRows,tcn1)
                in  
                    Result // Return a table with matching headers and first column and TRUE if match and FALSE if not match
            else "Incompatable Tables" // Return "Incompatable Tables" if headers and/or key columns don't match
in  
    Output

 

[kevdragon1]

Here's a function (I called it Table_Compare) to compare two tables that have matching headers and matching first columns:

(table1 as table, table2 as table) as any =>
let
    tcn1 = Table.ColumnNames(table1),
    tcn2 = Table.ColumnNames(table2),
    //Check if headers and key (first) columns match
    Output = if (tcn1=tcn2) and (Table.Column(table1,tcn1{0}) = Table.Column(table2,tcn2{0})) then
                let
                    List_Compare = (list1 as list, list2 as list) as list =>
                        List.Generate(()=>0,(x)=> (x)<List.Count(list1), (x) => x+1, (x)=> list1{x} = list2{x}),
                    ttr1 = Table.ToRows(Table.RemoveColumns(table1,tcn1{0})),
                    ttr2 = Table.ToRows(Table.RemoveColumns(table2,tcn2{0})),
                    ct = Table.RowCount(table1),
                    NewRows = List.Generate(()=>0, (x)=>x<ct,(x)=>x+1, (x)=> {Record.Field(table1{x},tcn1{0})} & List_Compare(ttr1{x}, ttr2{x})),
                    Result = Table.FromRows(NewRows,tcn1)
                in 
                    Result // Return a table with matching headers and first column and TRUE if match and FALSE if not match
            else "Incompatable Tables" // Return "Incompatable Tables" if headers and/or key columns don't match
in 
    Output

thank you so much for this. That's what I needed. If you have the time could you explain what the code is doing after this line: List_Compare = (list1 as list, list2 as list) as list => because I am new to M code and would like to learn

 

[JGordon11]

List_Compare is a function that returns a list of TRUEs and FALSEs depending on whether item(x) of list1 = item(x) of list2. It is called later in the NewRows step.

ttr1 is a list of lists. Each inner list is the contents of the corresponding row of table 1 (with the key (first) column skipped).
Similar for ttr2.

ct is self-explanatory

NewRows is a list of lists. Each inner list is the key column element for the corresponding row in table1 prefixing a list of TRUEs and FALSEs based on whether the ttr1 row element = ttr2 row element.

Result transforms NewRows into a table using the original column headers (tcn1)

If you want to see the steps, by clicking in the Applied Steps window, create a new query and change New and Old to the correct tables:

let 
    table1 = New, //YOUR TABLE1 NAME,
    table2 = Old, //YOUR TABLE2 NAME,
    tcn1 = Table.ColumnNames(table1),
    tcn2 = Table.ColumnNames(table2),
    List_Compare = (list1 as list, list2 as list) as list => 
        List.Generate(()=>0,(x)=> (x)<List.Count(list1), (x) => x+1, (x)=> list1{x} = list2{x}),
    ttr1 = Table.ToRows(Table.RemoveColumns(table1,tcn1{0})),
    ttr2 = Table.ToRows(Table.RemoveColumns(table2,tcn2{0})),
    ct = Table.RowCount(table1),
    NewRows = List.Generate(()=>0, (x)=>x<ct,(x)=>x+1, (x)=> {Record.Field(table1{x},tcn1{0})} & List_Compare(ttr1{x}, ttr2{x})),
    Result = Table.FromRows(NewRows,tcn1)
in  
    Result // Return a table with matching headers and first column and TRUE if match and FALSE if not match