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 All,
I recently saw yesterday's post from Fernando Elecno ( Transform Table Vertically ). I wanted to see if the following was possible using a similar approach using the unpivot function or PowerQuery feature. I have tried to manipulate it, however, I am running into issues. Logically, I want to do this
My Current Table:
PersonID | LampDemand | ChairDemand | TableDemand | LampEstimate | ChairEstimate | TableEstimate |
1000 | 24 | 12 | 6 | 12 | 6 | 3 |
1001 | 20 | 10 | 5 | 8 | 4 | 2 |
1002 | 16 | 8 | 4 | 4 | 2 | 1 |
My Desired Output/Requirement:
Example:
PersonID | Item/Type | Demand | Estimate |
1000 | Lamp | 24 | 12 |
1000 | Chair | 12 | 6 |
1000 | Table | 6 | 3 |
1001 | Lamp | 20 | 8 |
1001 | Chair | 10 | 4 |
1001 | Table | 5 | 2 |
1002 | Lamp | 16 | 4 |
1002 | Chair | 8 | 2 |
1002 | Table | 4 | 1 |
I want to not only transform the table vector vertically, however, I also want to manipulate and append the table structure. Relates the 3 items to the respective categories. For instance, list/group by Lamp, Chair, and Table as identifiers so that I can relate them to their "Demand" and "Estimate" values. I am wondering if PowerQuery is even the right approach or should I research to try and tackle this with R/Python.
Thank you very much in advance for any any notes, tips, and suggestions.
Best,
Aaron
Solved! Go to Solution.
Hi @Anonymous
yes, that's a thing for Power Query.
First you unpivot others (than the first column).
Then Split the Attribute-column to separate the item types from the value type, before
Pivoting back the value types.
Please paste the following code into the advanced editor and follow the steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIyARKGRkDCDJlhrBSrA1ZjCFIDUmgIIkyB2AKIQZqMYEpAegzNkGQgskBBpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [PersonID = _t, LampDemand = _t, ChairDemand = _t, TableDemand = _t, LampEstimate = _t, ChairEstimate = _t, TableEstimate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PersonID", Int64.Type}, {"LampDemand", Int64.Type}, {"ChairDemand", Int64.Type}, {"TableDemand", Int64.Type}, {"LampEstimate", Int64.Type}, {"ChairEstimate", Int64.Type}, {"TableEstimate", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"PersonID"}, "Attribute", "Value"),
#"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"Attribute.1", "Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.2]), "Attribute.2", "Value")
in
#"Pivoted Column"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Anonymous
yes, that's a thing for Power Query.
First you unpivot others (than the first column).
Then Split the Attribute-column to separate the item types from the value type, before
Pivoting back the value types.
Please paste the following code into the advanced editor and follow the steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIyARKGRkDCDJlhrBSrA1ZjCFIDUmgIIkyB2AKIQZqMYEpAegzNkGQgskBBpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [PersonID = _t, LampDemand = _t, ChairDemand = _t, TableDemand = _t, LampEstimate = _t, ChairEstimate = _t, TableEstimate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PersonID", Int64.Type}, {"LampDemand", Int64.Type}, {"ChairDemand", Int64.Type}, {"TableDemand", Int64.Type}, {"LampEstimate", Int64.Type}, {"ChairEstimate", Int64.Type}, {"TableEstimate", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"PersonID"}, "Attribute", "Value"),
#"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"Attribute.1", "Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.2]), "Attribute.2", "Value")
in
#"Pivoted Column"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you so much!! This worked, I took a look at the steps as well as the code. Very helpful. I will continue to sharpen my M code knowledge!
All the best and thank you again!
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.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |