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

[wdx223_Daniel · Accepted solution]

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]),
    Custom1 = #table({"Item Title","Item Number","Item Size","Price"},List.TransformMany(Table.ToRows(Source),each List.Transform(List.Zip(List.Split(List.Skip(_),3)),each {Text.Start(_{0},4),Text.Range(_{0},4),_{1}}),(x,y)=>{x{0}}&y))
in
    Custom1

[work_1111]

Hello - thanks for the help you've all provided. My requirements have slightly changed and I am seeking assistance. I still need to split the data but instead of splitting the columns by position, I need to split the column based on a  value in another column. I have a custom column "Common_Item_Num that finds the common value between 3 columns and provides that value. I would like to the split to happen based on this column since the lengths of the item num can vary.

Item Title	Small_Item Num	Medium_Item Num	Large_Item Num	Common_Item_Num	Small_Price	Medium_Price	Large_Price	Manufacturer	Location
Pants	PNTRSM	PNTRMD	PNTRLG	PNTR	1.57	2.28	2.99	Company A	Houston
Pants	PNTRSMP	PNTRMDP	PNTRLGP	PNTR	1.81	2.62	3.44	Company A	Houston
Shirts	TCHLSM	TCHLMD	TCHLLG	TCHL	2.65	3.65	4.65	Supplier 3	Dallas
Shirts	TCHLSMP	TCHLMDP	TCHLLGP	TCHL	3.22	4.22	5.22	Supplier 3	Dallas
Socks	9315RSM	9315RMD	9315RLG	9315R	0.54	0.65	0.77	Supplier 4	Phoenix

[dufoq3]

Hi @bkwok, try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc4xC4MwEIbhvyKZJZCYtDoWBS1oOaqbODg4FKIJVYf++56nZKnQ6f0C4eHalt2XYQwECxk8mmddHaPKjlHmOATXV4zkMqYkCSa1o+unT3DDXdh1XuzEuvAHBC+CJ4HMWBB2kZiIK/XP3P41aVHSkdugI7dBR6KkSaKoPfXqnHkN7yDCR9Yb089nIHgRPAmESUkYRe85Nbsv", 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, Manufacturer = _t, Location = _t]),
    YourSource = Source,
    ColNameSizeTable = Table.FromList(List.Distinct(List.Transform(List.Select(Table.ColumnNames(YourSource), each Text.Contains(_, "_Item", Comparer.OrdinalIgnoreCase)), (r)=> Text.BeforeDelimiter(r, "_"))), Splitter.SplitByNothing(), {"ColName"}),
    ColNameOtherList = List.Select(Table.ColumnNames(YourSource), each not List.ContainsAny(Text.SplitAny(_," _-/|:,;[]{}()"), ColNameSizeTable[ColName])),
    StepBack = ColNameSizeTable,
    #"Added Index" = Table.AddIndexColumn(StepBack, "Index", 1, 1, Int64.Type),
    Ad_SizeTables = Table.AddColumn(#"Added Index", "Size Tables", each
    [v_sizeTables = Table.SelectColumns(YourSource, ColNameOtherList & List.Select(Table.ColumnNames(YourSource), (b)=> Text.Contains(b, [ColName]))),
     v_adIndex =Table.AddIndexColumn(v_sizeTables, "InnerIndex", [Index], Table.RowCount(v_sizeTables)),
     v_toColumns = Table.ToColumns(v_adIndex)
    ][v_toColumns]),
    toTable =   [v_combinedLists = List.Transform(List.Zip(Ad_SizeTables[Size Tables]), each List.Combine(_)),
   v_table = Table.FromColumns(v_combinedLists, ColNameOtherList & {"Item Number", "Price", "Index"})
  ][v_table],
    #"Split Column by Positions" = Table.SplitColumn(toTable, "Item Number", Splitter.SplitTextByPositions({0, 4}), {"Item Number", "Item Size"}),
    #"Sorted Rows" = Table.Sort(#"Split Column by Positions",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

[work_1111]

@dufoq3 I've tried your updated formula but it still doesn't pull in the newly added columns. Also, the gear icon disappeared from the YourSource step.

[dufoq3]

@work_1111, use my latest code here.

1. Import your data and name your query as MyData
2. Edit 2nd step YourSource = Source and change Source to MyData

Now, my query is refering to your data table called MyData, so you can delete first step Source in my query (but it is not necessary - PowerQuery will ignore that step).

[dufoq3 · Accepted solution]

Hi, I'm not that skilled as @wdx223_Daniel but I built it my way:

Change 2nd step YourSource = Source (replace Source with your table reference)

//Move Data from Columns to Rows and split 1 column data
//https://community.fabric.microsoft.com/t5/Power-Query/Move-Data-from-Columns-to-Rows-and-split-1-column-data/m-p/3644632#M119982

let
    Source = Table.TransformColumnNames(Table.PromoteHeaders(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1BCoMwFATQq0jWIpgmra5baAsqn+pOpEgbJGA2ovdvMoZYwdVMJjx+27LnrEzU6HlULGZRVJt+HN8Yq8VgKtVXL2a/Ff00qP20Spr0R/2zbVjN+u5ifzm1X1Q1r7r0pbz5UtxtSRN5scETniHy/IhSsBQwQWcp2JnbOCVCbNotzfVR4LArOOwKDlsjYRDCxQGlYClgAuMcDCFddN0P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t])), Text.Trim),
    YourSource = Source,
    ColNameContains = Table.FromList(List.Distinct(List.Transform(List.Select(Table.ColumnNames(YourSource), each Text.Contains(_, "_Item", Comparer.OrdinalIgnoreCase)), (r)=> Text.BeforeDelimiter(r, "_"))), Splitter.SplitByNothing(), {"ColName"}),
    #"Added Index" = Table.AddIndexColumn(ColNameContains, "Index", 1, 1, Int64.Type),
    Ad_SizeTables = Table.AddColumn(#"Added Index", "Size Tables", each
    [v_sizeTables = Table.SelectColumns(YourSource, {"Item Title"} & List.Select(Table.ColumnNames(YourSource), (r)=> Text.Contains(r, [ColName]))),
     v_adIndex =Table.AddIndexColumn(v_sizeTables, "InnerIndex", [Index], Table.RowCount(v_sizeTables)),
     v_toColumns = Table.ToColumns(v_adIndex)
    ][v_toColumns]),
    toTable =   [v_combinedLists = List.Transform(List.Zip(Ad_SizeTables[Size Tables]), each List.Combine(_)),
   v_table = Table.FromColumns(v_combinedLists, {"Item", "Item Number", "Price", "Index"})
  ][v_table],
    #"Split Column by Positions" = Table.SplitColumn(toTable, "Item Number", Splitter.SplitTextByPositions({0, 4}), {"Item Number", "Item Size"}),
    #"Sorted Rows" = Table.Sort(#"Split Column by Positions",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

[work_1111]

@dufoq3 thank you! I was able to apply this query to get my data in the format I needed. I was able to understand each step and make additional adjustments as needed. I just have one question, if I needed to added a few more columns to my table would I just  update this part of the query by adding _t, #"(blank).7" and so on?

[dufoq3]

HI @work_1111, you can use this code and edit Sample Data with gear icon:

Use this code please. I've splitted 1st step to 3. Now you are able to use gear icon. You should still refer to your data in step YourSource as I mentioned before.

To answer your question. No, it is not eddnough by adding _t..., because you should not edit this step in Advanced Editor - that step (Source) is just sample data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1BCoMwFATQq0jWIpgmra5baAsqn+pOpEgbJGA2ovdvMoZYwdVMJjx+27LnrEzU6HlULGZRVJt+HN8Yq8VgKtVXL2a/Ff00qP20Spr0R/2zbVjN+u5ifzm1X1Q1r7r0pbz5UtxtSRN5scETniHy/IhSsBQwQWcp2JnbOCVCbNotzfVR4LArOOwKDlsjYRDCxQGlYClgAuMcDCFddN0P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    TrimmedColumnNames = Table.TransformColumnNames(#"Promoted Headers", Text.Trim),
    YourSource = TrimmedColumnNames,
    ColNameContains = Table.FromList(List.Distinct(List.Transform(List.Select(Table.ColumnNames(YourSource), each Text.Contains(_, "_Item", Comparer.OrdinalIgnoreCase)), (r)=> Text.BeforeDelimiter(r, "_"))), Splitter.SplitByNothing(), {"ColName"}),
    #"Added Index" = Table.AddIndexColumn(ColNameContains, "Index", 1, 1, Int64.Type),
    Ad_SizeTables = Table.AddColumn(#"Added Index", "Size Tables", each
    [v_sizeTables = Table.SelectColumns(YourSource, {"Item Title"} & List.Select(Table.ColumnNames(YourSource), (r)=> Text.Contains(r, [ColName]))),
     v_adIndex =Table.AddIndexColumn(v_sizeTables, "InnerIndex", [Index], Table.RowCount(v_sizeTables)),
     v_toColumns = Table.ToColumns(v_adIndex)
    ][v_toColumns]),
    toTable =   [v_combinedLists = List.Transform(List.Zip(Ad_SizeTables[Size Tables]), each List.Combine(_)),
   v_table = Table.FromColumns(v_combinedLists, {"Item", "Item Number", "Price", "Index"})
  ][v_table],
    #"Split Column by Positions" = Table.SplitColumn(toTable, "Item Number", Splitter.SplitTextByPositions({0, 4}), {"Item Number", "Item Size"}),
    #"Sorted Rows" = Table.Sort(#"Split Column by Positions",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

[work_1111]

@dufoq3 I'ved followed your steps the 2 additional columns are showing in my updated source, but not in the final table output. See image below for the 2 additional columns:

tem Title	Small_Item Num	Medium_Item Num	Large_Item Num	Small_Price	Medium_Price	Large_Price	Manufacturer	Location
Item 1	PNTRSM	PNTRMD	PNTRLG	1.57	2.28	2.99	Company A	Houston
Item 1	PNTRSMP	PNTRMDP	PNTRLGP	1.81	2.62	3.44	Company A	Houston
Item 2	TCHLSM	TCHLMD	TCHLLG	2.65	3.65	4.65	Supplier 3	Dallas
Item 2	TCHLSMP	TCHLMDP	TCHLLGP	3.22	4.22	5.22	Supplier 3	Dallas

[spinfuzer]

Just replace source with whatever your actual source is.  The json binary thing is just a manual table created when you Press "Enter Data" in the Power Query Editor.  You can edit this manual table by clicking on the COG in the Source applied step.

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}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item Title"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Item Title", "Index"}, 
    {{"Rows", 
        each Table.FromColumns(List.Split(_[Value],3), {"Item Num", "Price"})
    }}, 
    GroupKind.Local, 
    (x,y) => Byte.From(x[Index] + 6 = y[Index])
    ),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Item Num", "Price"}, {"Item Num", "Price"}),
    #"Split Column by Position" = Table.SplitColumn(#"Expanded Rows", "Item Num", Splitter.SplitTextByPositions({0, 4}, false), {"Item Num.1", "Item Num.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Item Num.1", type text}, {"Item Num.2", type text}})
in
    #"Changed Type1"

Key here is

1) Unpivot Other Columns than the 1st column

2) Add index from 0

3) Group By Item Title and Index

4) Group by Add 4th parameter GroupKind.Local

5) Group by 5th parameter.  custom grouping (x,y) =>(x,y) => Byte.From(x[Index] + 6 = y[Index])

6) Expand and split by 4 characters

[work_1111]

@wdx223_Daniel Is there another way to accomplish this without the binary portion?

[wdx223_Daniel · Accepted solution]

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]),
    Custom1 = #table({"Item Title","Item Number","Item Size","Price"},List.TransformMany(Table.ToRows(Source),each List.Transform(List.Zip(List.Split(List.Skip(_),3)),each {Text.Start(_{0},4),Text.Range(_{0},4),_{1}}),(x,y)=>{x{0}}&y))
in
    Custom1