Aggregate non-matching values

[raprabhakaran]

Update 16/04

Hello Community,

I just realiased the title Remove Matching Rows & Aggregate non-matching values may be misleading and not required for my solution. Below is the updated requirement:

I am working on a system migration project. I have two table one from legacy system and another from the new system. Both tables should ideally contain identical values as they are standardized.

My challenge: 

I'd like to create a new query within Power Query - that compares the values in LegacyTable with that of NewSysTable. The common identifier between both the table will be Row_ID.

Below is tabular representation of my expected output:

Row_ID	Column_1	Column_2
1131	0	1
1133	0	1
1136	0	0

1  =  Indicates a mismatch between the Column value corresponding to the Row_ID in both tables

0 = Indicates a successful migration, where the column values corresponding to the Row_ID match and can be disregarded from error aggregation

Based on the below two inputs:

LegacyTable

Row_ID	Column_1	Column_2
1131	Apple	Orange
1133	Kale	Banana
1136	Papa	Romeo

NewSysTable

Row_ID	Column_1	Column_2
1131	Apple	Banana
1133	Kale	Orange
1136	Papa	Romeo

Thanks in advance for your help and guidance!

[lbendlin]

What exactly are you trying to compare? Column types or cell contents?

[raprabhakaran]

Hi @lbendlin 

I am trying to compare the values. 

For Eg: 

LegacyTable

Row_ID	Column_1	Column_2
1131	Apple	Orange
1133	Kale	Banana
1136	Papa	Romeo

NewSysTable

Row_ID	Column_1	Column_2
1131	Apple	Banana
1133	Kale	Orange
1136	Papa	Romeo

Expected Output

Row_ID	Column_1	Column_2
1131	0	1
1133	0	1
1136	0	0

Thanks for the help!

[Ashish_Mathur]

Hi,

Merge into Table1, data from Table2.  Merge as a new Table.  The connecting column between both tables will be the ID column.  In the Query Editor, write a custom formula to check if Column_1=Column_1.1.

[Anonymous · Accepted solution]

Thanks @lbendlin 

Hi, @raprabhakaran 

Have you solved the current problem? Based on the sample data you provide and the output you expect, I do this by the following method:

LegacyTable

NewSysTable

I've created two measures as follows:

column1 =
VAR _id =
    SELECTEDVALUE ( LegacyTable[Row_ID] )
VAR _column1_of_LegacyTable =
    CALCULATE ( MAX ( 'LegacyTable'[Column_1] ), 'LegacyTable'[Row_ID] = _id )
VAR _column1_of_NewSysTable =
    CALCULATE ( MAX ( 'NewSysTable'[Column_1] ), 'NewSysTable'[Row_ID] = _id )
RETURN
    IF ( _column1_of_LegacyTable = _column1_of_NewSysTable, 0, 1 )

column2 =
VAR _id =
    SELECTEDVALUE ( LegacyTable[Row_ID] )
VAR _column2_of_LegacyTable =
    CALCULATE ( MAX ( 'LegacyTable'[Column_2] ), 'LegacyTable'[Row_ID] = _id )
VAR _column2_of_NewSysTable =
    CALCULATE ( MAX ( 'NewSysTable'[Column_2] ), 'NewSysTable'[Row_ID] = _id )
RETURN
    IF ( _column2_of_LegacyTable = _column2_of_NewSysTable, 0, 1 )

I put these two measures and the Row_ID columns of LegacyTable into a table visual, and the result is as follows:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.