Power Query / Power BI Transform Data problem: every new item under a category into its own column?

[Adairene]

Hello!

Please help! Is there any way I can do the following in Power Query / Power BI's Transform Data?

Column1	Column2	Column3
A	1	X
A	1	Y
A	1	Z
B	1	X
C	1	X

into

Column1	Column2	Column3	Column4	Column5	Column6
A	1	X	X	Y	Z
A	1	Y	X	Y	Z
A	1	Z	X	Y	Z
B	1	X	X	X	X
C	1	X	X	X	X

So every new occurence (X, Y Z) under a certain category (A) should go to its own column?

Thank you in advance!

[edhans]

Glad I was able to help @Adairene 

[Adairene]

Thank you so much @edhans, this got me where I wanted to be! 🙂

[edhans · Accepted solution]

@Adairene - this will get you most of what you want:

becomes this:

The issue is, for B/C, where there were no values to expand, it would need to figure out how many times to replicate that value.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIE4gilWB0ELxKFFwXmOaGodEbwYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column1", "Column2"}, {{"All Rows", each _, type table [Column1=nullable text, Column2=nullable text, Column3=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Column3 Expansion", each [All Rows][Column3]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Column3 Expansion", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column3 Expansion", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column3 Expansion.1", "Column3 Expansion.2", "Column3 Expansion.3"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"All Rows", "Column3 Expansion.1", "Column3 Expansion.2", "Column3 Expansion.3"}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Removed Other Columns", "All Rows", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
    #"Transposed Table" = Table.Transpose(#"Expanded All Rows"),
    #"Transposed Table1" = Table.Transpose(#"Transposed Table")
in
    #"Transposed Table1"

There is no Fill Right feature. There is a fill down, but even transposing the table to do that would cause issues as the number of columns to fill down becomes dynamic as well. I would need to understand a bit more about the logic of what you are doing. You seem to be denormalizing the data,which is unusual for Power BI.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[AlB]

Hi @Adairene 

You'll have to explain a bit more the logic behind the transformation. I don't understand how you get from one table to the other

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers