Help with merging values on a specific column in a new table

[daniel_t_93_]

Hi, 

I have the following dataset (simplified)

ID	Value	Step 1	Step 2	Step 3
1	A	A
1	B		B
1	C			C
2	A	A
3	A	A
3	D		D
3	G			G
4	A	A
4	F		F

Step 1, 2 and 3 are derived from the value in the column 'value', using IF statements.

What I want is, in a new table, the following:

ID	Step 1	Step 2	Step 3
1	A	B	C
2	A
3	A	D	G
4	A	F

I tried this using a LOOKUPVALUE function, but that obviously doesn't work since it matches on multiple values (also the blanks). I also tried a CALCULATE, FIRSTNONBLANK and FILTER combination, but that also didn't work.

As you can see, step 2 and 3 can take up different values, or have no value at all. This happens when a person didn't get to that step at all. 

Anyone has some advice on how to achieve this? 

Thanks in advance!

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", type text}, {"Step 1", type text}, {"Step 2", type text}, {"Step 3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Value"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"ID"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Hope this helps.