cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Adairene
Frequent Visitor

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

Hello!

 

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

 

Column1Column2Column3
A1X
A1Y
A1Z
B1X
C1

X

 

into

 

Column1Column2Column3Column4Column5Column6
A1XXYZ
A1YXYZ
A1ZXYZ
B1XXXX
C1XXXX

 

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

 

Thank you in advance!

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

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

edhans_0-1607977149616.png

becomes this:

edhans_1-1607977225066.png

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User III
Super User III

Glad I was able to help @Adairene 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Adairene
Frequent Visitor

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

edhans
Super User III
Super User III

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

edhans_0-1607977149616.png

becomes this:

edhans_1-1607977225066.png

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

AlB
Super User III
Super User III

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 

SU18_powerbi_badge

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors