Move Data from Columns to Rows and split 1 column data

[work_1111]

Hello everyone,

I have a huge set of data in a table where a single item has multiple part numbers and prices. See sample below under Current table. I am trying to get the data into more of a pivot like structure while also separating the core part number from the size. See Desired Table. Please share any tips or guidance on how I can achieve this. Thank you!

Current table

Item Title	Small_Item Num	Medium_Item Num	Large_Item Num	Small_Price	Medium_Price	Large_Price
Item 1	PNTRSM	PNTRMD	PNTRLG	1.57	2.28	2.99
Item 1	PNTRSMP	PNTRMDP	PNTRLGP	1.81	2.62	3.44
Item 2	TCHLSM	TCHLMD	TCHLLG	2.65	3.65	4.65
Item 2	TCHLSMP	TCHLMDP	TCHLLGP	3.22	4.22	5.22

Desired Table

Item Title	Item Number	Item Size	Price
Item 1	PNTR	SM	1.57
Item 1	PNTR	MD	2.28
Item 1	PNTR	LG	2.99
Item 1	PNTR	SMP	1.81
Item 1	PNTR	MDP	2.62
Item 1	PNTR	LGP	3.44
Item 2	TCHL	SM	2.65
Item 2	TCHL	MD	3.65
Item 2	TCHL	LG	4.65
Item 2	TCHL	SMP	3.22
Item 2	TCHL	MDP	4.22
Item 2	TCHL	LGP	5.22

[Anonymous · Accepted solution]

Hi @work_1111 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc27CoAwDIXhV5HOUmhsvcwKKqgEdZOOjm6+PzaHUged/kPgI8ehxvu8MqNyxcu+bnMccxfH1IdhtKtCSFONNI3y+YdyspwwQ9cGrKSQQlv7arns7TDhsQw8loHHwTgYxEp+KCfLCTMYERjiJN4/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Title" = _t, #"Small_Item Num" = _t, #"Medium_Item Num" = _t, #"Large_Item Num" = _t, Small_Price = _t, Medium_Price = _t, Large_Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Title", type text}, {"Small_Item Num", type text}, {"Medium_Item Num", type text}, {"Large_Item Num", type text}, {"Small_Price", type number}, {"Medium_Price", type number}, {"Large_Price", type number}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Small_Item Num", "Item Title", "Small_Price"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Item Title", "Small_Item Num", "Small_Price"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Small_Item Num", "Item Number"}, {"Small_Price", "Price"}}),
    Custom1 = Table.SelectColumns(#"Changed Type",{"Large_Item Num", "Item Title", "Large_Price"}),
    #"Reordered Columns1" = Table.ReorderColumns(Custom1,{"Item Title", "Large_Item Num", "Large_Price"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Large_Item Num", "Item Number"}, {"Large_Price", "Price"}}),
    Custom2 = Table.SelectColumns(#"Changed Type",{"Item Title", "Medium_Item Num", "Medium_Price"}),
    #"Renamed Columns2" = Table.RenameColumns(Custom2,{{"Medium_Item Num", "Item Number"}, {"Medium_Price", "Price"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns2", #"Renamed Columns1",#"Renamed Columns"}),
    #"Split Column by Position" = Table.SplitColumn(#"Appended Query", "Item Number", Splitter.SplitTextByPositions({0, 4}, false), {"Item Number.1", "Item Number.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Item Number.1", type text}, {"Item Number.2", type text}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type1",{{"Item Number.1", "Item Number"}, {"Item Number.2", "Item Size"}})
in
    #"Renamed Columns3"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dufoq3 · Accepted solution]

Your task has been solved here.

[Anonymous · Accepted solution]

Hi @work_1111 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc27CoAwDIXhV5HOUmhsvcwKKqgEdZOOjm6+PzaHUged/kPgI8ehxvu8MqNyxcu+bnMccxfH1IdhtKtCSFONNI3y+YdyspwwQ9cGrKSQQlv7arns7TDhsQw8loHHwTgYxEp+KCfLCTMYERjiJN4/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Title" = _t, #"Small_Item Num" = _t, #"Medium_Item Num" = _t, #"Large_Item Num" = _t, Small_Price = _t, Medium_Price = _t, Large_Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Title", type text}, {"Small_Item Num", type text}, {"Medium_Item Num", type text}, {"Large_Item Num", type text}, {"Small_Price", type number}, {"Medium_Price", type number}, {"Large_Price", type number}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Small_Item Num", "Item Title", "Small_Price"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Item Title", "Small_Item Num", "Small_Price"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Small_Item Num", "Item Number"}, {"Small_Price", "Price"}}),
    Custom1 = Table.SelectColumns(#"Changed Type",{"Large_Item Num", "Item Title", "Large_Price"}),
    #"Reordered Columns1" = Table.ReorderColumns(Custom1,{"Item Title", "Large_Item Num", "Large_Price"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Large_Item Num", "Item Number"}, {"Large_Price", "Price"}}),
    Custom2 = Table.SelectColumns(#"Changed Type",{"Item Title", "Medium_Item Num", "Medium_Price"}),
    #"Renamed Columns2" = Table.RenameColumns(Custom2,{{"Medium_Item Num", "Item Number"}, {"Medium_Price", "Price"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns2", #"Renamed Columns1",#"Renamed Columns"}),
    #"Split Column by Position" = Table.SplitColumn(#"Appended Query", "Item Number", Splitter.SplitTextByPositions({0, 4}, false), {"Item Number.1", "Item Number.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Item Number.1", type text}, {"Item Number.2", type text}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type1",{{"Item Number.1", "Item Number"}, {"Item Number.2", "Item Size"}})
in
    #"Renamed Columns3"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pratyashasamal]

Hi @work_1111 ,
Step 1 - Open the loaded table in Power Query Editor
Step 2 - Select the columns Small_Item Num , Medium_Item Num and Large_Item Num and click on Unpivot columns. 
Step 3 - Now again select Small_Price , Medium_Price and Large_Price and click on Unpivot Column
The result will be something like this:-

Step 4 - Now remove the Attribute and Attribute.1 column from the resulting table.
Step 5 -Now select the Value column and the select Split Column > By Number of Character . Enter 4 into the dialog box.
Step 6 - Rename the columns as required . 

Please let me know if this was helpful.
Thanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[work_1111]

@pratyashasamal I get the above error. Is the issue because the source is SharePoint? Do I need to do another step first?

[work_1111]

@pratyashasamal At step 2, I selected the 3 columns and clicked Unpivot columns, all of my data says error now and the 3 columns I unpivoted disappeared so I cannot continue to step 3. Is there a step that I missed somehow? 

[pratyashasamal]

Here is the m code :-
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc3RCoIwGAXgV5Fdi+DS0uuCClR+0rsxQmrEYLsRff+2o8wkQThnRz5+IRiL/z4ZC3YflY06PRrllihqbW/ME2MzWUy1euvJbreqHz5qO82SBv1Sv2wdZjO/w+XU/aKme7T1UurLUqqrK2mSn1zwhBeIstyjFCwFTNBFCnbkLg5Jlq3aL935VuGwLzjsCw47k8MgMh87lIKlgAmMczBE7kPKLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item Title", type text}, {" Small_Item Num", type text}, {" Medium_Item Num", type text}, {" Large_Item Num", type text}, {" Small_Price", type number}, {" Medium_Price", type number}, {" Large_Price", type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Item Title", " Small_Price", " Medium_Price", " Large_Price"}, "Attribute", "Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Columns",{"Item Title", "Attribute", "Value", " Small_Price", " Medium_Price", " Large_Price"}),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Item Title", "Attribute", "Value"}, "Attribute.1", "Value.1"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns1",{"Attribute", "Attribute.1"}),
#"Split Column by Position" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByRepeatedLengths(4), {"Value.1.1", "Value.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Value.1.1", type text}, {"Value.2", type text}})
in
#"Changed Type2"

[AUDISU]

Hi @work_1111 ,

Please reffer following video.
https://www.youtube.com/watch?v=e0Rn8HRr-0M

Thanks.