Forum Discussion
Aggregate non-matching values
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!
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.
4 Replies
What exactly are you trying to compare? Column types or cell contents?
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!
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.
