Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGlad I was able to help @Adairene
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.