Forum Discussion
Unmerging dataset
Hi there,
I have data that is strangely structured like the following:
ColumnA ColumnB
A B
B C
and I want to find a way to conver the data to the following:
ColumnA ColumnB ColumnC
A B C
Not quite sure where to start, but the logic should be something like if columnB value equals any row value in columnA, then columnC equals columnB value where match was made.
(Also if anyone knows what the first data reference model is referred to, that'd be great so I can investigate it more)
Thanks in advance!
Following up with this thread that I have solved my issue via merging my table with the same table referencing column B as column A
6 Replies
Following up with this thread that I have solved my issue via merging my table with the same table referencing column B as column A
Hi Nate,
using your formula as: = Table.AddColumn(#"Changed Type", "Custom", each if List.Contains(#"Changed Type"[Column1], [Column2]) then [Column2] else null)
It yields the following result:
Column1 Column2 Custom
A B B
B C null
Is there a way to pull the column2 value relevant to the row in which it matched from column1?Also please let me know if there's an alternative way as this function seems to be very slow on my larger dataset
Hi CHARLEA1
Did you try using a custom function to generate column C? This function can take values from column B and check against column A.
I can visualise the steps in my mind and guess it will work.
Thanks
Hi CHARLEA1 ,
You may try the pivot/unpivot feature.
Input:
Output:
1.Select two columns and click 'Unpivot Columns'.
2.Remove the Attribute column.
3.Remove duplicates.
4.Add an index column.
5.Pivot the Index column. Select 'Don't Aggregate' in the Aggregate Value Function.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
