Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I am new to Power Query and need a little help. Below are two tables with the first represententing how my data is currently structured, and the second representing how I need to to be transformed. I know that I can create separate tables for each pair (one for A, one for B, etc.), and then append them all together at the end with the matching columns. However, I wanted to see if there is a more efficient way to do it?
Name | A-K | A-E | B-K | B-E | C-K | C-E | D-K | D-E |
Tyler | 1.1 | 3.1 | 1.7 | 1.5 | 4.7 | 1.2 | 4.5 | 3.0 |
Sarah | 3.4 | 2.4 | 3.9 | 2.9 | 3.5 | 1.5 | 2.5 | 2.1 |
Lara | 2.6 | 4.9 | 4.1 | 3.6 | 2.9 | 2.4 | 1.5 | 2.9 |
Jimmy | 3.1 | 5.0 | 1.1 | 4.1 | 1.6 | 3.9 | 3.5 | 1.1 |
Name | “Attribute” | K | E |
Tyler | A | 1.1 | 3.1 |
Sarah | A | 3.4 | 2.4 |
Lara | A | 2.6 | 4.9 |
Jimmy | A | 3.1 | 5.0 |
Tyler | B | 1.7 | 1.5 |
Sarah | B | 3.9 | 2.9 |
Solved! Go to Solution.
See if this is what you need
Here is what I did:
Selected the Name column and unpivoted other columns. I get this:
I then selected the Attribute column, Transform Ribbon, Split Column by the - character.
Change Value column to a number, then I selecgted the Attribute.2 column, and on the transform ribbon, Pivot Column with these settings:
You can then rename the columns to what you need them to be.
Full code sample here:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY47CoAwEETvsrUENj/JFcROu5AihaCgjZ23dx2TbR6TwJudnGl9zu2mgdiw0IFsRjAIfcsW+ftxVIZMS73rDsMLLehMQk7IQTtsI8OcxcQzojKB//Go+l/Y9QRxOq7r0ZFBR/s2OuqEflwOlhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"A-K" = _t, #"A-E" = _t, #"B-K" = _t, #"B-E" = _t, #"C-K" = _t, #"C-E" = _t, #"D-K" = _t, #"D-E" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", Currency.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.2]), "Attribute.2", "Value")
in
#"Pivoted Column"
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 ReportingSee if this is what you need
Here is what I did:
Selected the Name column and unpivoted other columns. I get this:
I then selected the Attribute column, Transform Ribbon, Split Column by the - character.
Change Value column to a number, then I selecgted the Attribute.2 column, and on the transform ribbon, Pivot Column with these settings:
You can then rename the columns to what you need them to be.
Full code sample here:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY47CoAwEETvsrUENj/JFcROu5AihaCgjZ23dx2TbR6TwJudnGl9zu2mgdiw0IFsRjAIfcsW+ftxVIZMS73rDsMLLehMQk7IQTtsI8OcxcQzojKB//Go+l/Y9QRxOq7r0ZFBR/s2OuqEflwOlhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"A-K" = _t, #"A-E" = _t, #"B-K" = _t, #"B-E" = _t, #"C-K" = _t, #"C-E" = _t, #"D-K" = _t, #"D-E" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", Currency.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.2]), "Attribute.2", "Value")
in
#"Pivoted Column"
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 Reporting