Forum Discussion
Helper IIITranspose 1 column based on distinct values
Hi all.
I have a fairly large piece of data and i'm looking transform it like below
In the original data each customer ID has seperate rows for each Value they have, what I want is to have 1 row per Customer ID, transposing the values into colums based on the unique customer ID. Not all Customers have the same number of values.
Original:
| ID | Value |
| 1111111 | A |
| 1111111 | B |
| 1111111 | C |
| 2222222 | A |
| 2222222 | B |
| 2222222 | C |
| 2222222 | D |
| 3333333 | A |
What I want:
| ID | Value 1 | Value 2 | Value 3 | Value 4 |
| 1111111 | A | B | C | |
| 2222222 | A | B | C | D |
| 3333333 | A |
Is there anyway I can do this in PowerBI? any suggestions would be greatly appreciated.
thanks
S 🙂
sarah2_williams , The easy way is , duplicate the column and unpivot and pivot the two column. Rename new columns
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMoQAJR0lR6VYHWS+ExrfGcw3ggC4egTfCY2Prt4FzDeGAIj+WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Value", "Value - Copy"), #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Value]), "Value", "Value - Copy", List.Max) in #"Pivoted Column"The other way is , Create a new query by right click on value column -> Remove duplicate -> convert to table- > add index column , Create new column "value " & [Index]
Merge it with the previous table and get the value column. now unpivot Value and this new column
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
2 Replies
- amitchandak
Super User
sarah2_williams , The easy way is , duplicate the column and unpivot and pivot the two column. Rename new columns
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMoQAJR0lR6VYHWS+ExrfGcw3ggC4egTfCY2Prt4FzDeGAIj+WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Value", "Value - Copy"), #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Value]), "Value", "Value - Copy", List.Max) in #"Pivoted Column"The other way is , Create a new query by right click on value column -> Remove duplicate -> convert to table- > add index column , Create new column "value " & [Index]
Merge it with the previous table and get the value column. now unpivot Value and this new column
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
sarah2_williamsThakn you.
When you say can you clear up what I have put in brackets? "The easy way is , duplicate the column(which column) and unpivot and pivot the two column(which two columns). Rename new columns"