Unmerging dataset

[CHARLEA1]

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!

[CHARLEA1 · Accepted solution]

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

[Anonymous]

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.

[Thingsclump]

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

www.thingsclump.com

[CHARLEA1]

Also please let me know if there's an alternative way as this function seems to be very slow on my larger dataset

[CHARLEA1]

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?

[Anonymous]

= Table.AddColumn(PriorStepOrTableName, "Column C", each if List.Contains(PriorStepOrTableName[Column B], [Column A]) then [Column B] else null))

--Nate