Forum Discussion
Removing duplicates from a column, creating new columns for each distinct value
Sorry, I'm confident there is an answer to this question somewhere on the community but I'm new to Power BI and am struggling to express my problem in the right termanology.
Essentially, in the Power Query Editor I am looking to go from a table like this:
| ID | Value |
| 1 | A |
| 2 | A |
| 3 | A |
| 3 | B |
| 3 | C |
| 4 | A |
| 4 | B |
| ID | A | B | C |
| 1 | A | ||
| 2 | A | ||
| 3 | A | B | C |
| 4 | A | B |
Or something to that affect. The problem arises when I am creating a table in a report. When I add the Value column it creates another row with the same ID, whereas I want the rows to be a unique list of IDs with all Values having their own seperate column.
Hope that makes sense and thank you for any help.
Alex
You could duplicate [Value] and pivot the duplicated column with no aggregation and values from [Value]
7 Replies
- Smauro
Solution Sage
You could duplicate [Value] and pivot the duplicated column with no aggregation and values from [Value]
et Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjKCs4xRWE5wljOYZQKXNYHIxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"lst", each _[Value]}}), #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"lst", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "lst", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"lst.1", "lst.2", "lst.3"},"") in #"Split Column by Delimiter"
chrschmiMicrosoft Employee
Will creating a matrix instead of a table work for you? In the visualizations pane simply select the visual next to the table one. Then add the "Value" column in your example under the Columns and then drag/drop again under the Values section, like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjKCs4xRWE5wljOYZQKXNYHIxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"lst", each Table.Transpose(_[[Value]])}}), #"Expanded lst" = Table.ExpandTableColumn(#"Grouped Rows", "lst", {"Column1", "Column2", "Column3"}, {"A", "B", "C"}) in #"Expanded lst"
yet antother way, via record functions
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjKCs4xRWE5wljOYZQKXNYHIxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"lst", each Record.FromList(_[Value],_[Value])}}), #"Expanded lst" = Table.ExpandRecordColumn(#"Grouped Rows", "lst", List.Distinct(Source[Value])) in #"Expanded lst"