Forum Discussion
Solved
Help with merging values on a specific column in a new table
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!
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.
1 Reply
- Ashish_Mathur
Super User
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.