Transform, Manipulate, Append Table Vertically

[Anonymous]

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

[ImkeF · Accepted 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"

[Anonymous]

@ImkeF 

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!