Forum Discussion
Matching alternate columns in powerbi
Hi Team,
As shown I have table with more than 60 columns which contains two sets of data in alternate columns, I need to find the unmatched values in the columns and get output as shown
Output:
Thanks & Regards
JEN
Hi jenani_user ,
Here are the steps you can follow：
1. In power query – select [A.acc], [B.acc], [A.text], [A.text].
2. Select [Value] – Group By.
3. Select [ALL] – Select the columns you want.
4. Select [Count] -- 1.
5. Select [Count] and [ALL.A.id] – Remove Columns.
6. Select [ALL.Attribute] – Pivor Column.
7. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Hi jenani_user ,
Here are the steps you can follow：
1. Create calculated table.
Table 2 = var _table1= FILTER( 'Table', 'Table'[A.acc]<>'Table'[B.acc]) var _table1_1= SUMMARIZE( _table1,[A.acc],[B.acc]) var _table2= FILTER( 'Table', 'Table'[A.text]<>'Table'[B.text]) var _table2_1= SUMMARIZE( _table2,[A.text],[B.text]) return CROSSJOIN( _table1_1,_table2_1)2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
HI,
Thanks for the response,In this case,If the table has only few columns then this would be fine whereas the actual table contains more 60 columns, Could suggest any other ways to compare and create dynamically?
Thanks & Regards,
Jen
Hi jenani_user ,
Here are the steps you can follow：
1. In power query – select [A.acc], [B.acc], [A.text], [A.text].
2. Select [Value] – Group By.
3. Select [ALL] – Select the columns you want.
4. Select [Count] -- 1.
5. Select [Count] and [ALL.A.id] – Remove Columns.
6. Select [ALL.Attribute] – Pivor Column.
7. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
