Trouble with Transpose

[jboschee]

Hi everyone,

I'm trying to transpose my raw data and get it in a specific format.  I need to transpose the raw data so that the Website Sku is at the beginning of the row, followed by the sum of the Component Qty.  Then whatever number is in the Component Qty field, list the UPC and dimensions that many times until all are accounted for.

Thanks in advance!

RAW DATA
Website Sku	Component Qty	Component Weight (Lbs)	Component Height (in)	Component Length (in)	Component Width (in)	UPC	Box Count
AB-CD-DD-1234	2	4	4	8	9	00810099382297	4
AB-CD-DD-1234	1	36	13	32	22	00810099382181	4
AB-CD-DD-1234	1	1	0	9	6	00810099382600	4
ZXY-FHR-6424	2	1	3	10	3	00810099381108	5
ZXY-FHR-6424	1	1	0	9	6	00810099380026	5
ZXY-FHR-6424	1	19	9	30	23	00810047869283	5
ZXY-FHR-6424	1	61	18	31	31	00810047869320	5
FORMAT NEEDED
vendor_sku	number_of_boxes	box_1_upc	shipping_weight1	package_height1	package_length1	package_width1	box_2_upc	shipping_weight1	package_height1	package_length1	package_width1	box_3_upc	shipping_weight1	package_height1	package_length1	package_width1	box_4_upc	shipping_weight1	package_height1	package_length1	package_width1	box_5_upc	shipping_weight1	package_height1	package_length1	package_width1
AB-CD-DD-1234	4	00810099382297	4	4	8	9	00810099382297	4	4	8	9	00810099382181	36	13	32	22	00810099382600	1	0	9	6
ZXY-FHR-6424	5	00810099381108	1	3	10	3	00810099381108	1	3	10	3	00810099380026	1	0	9	6	00810047869283	19	9	30	23	00810047869320	61	18	31	31

[jgeddes]

This can be done in Power Query with the following code.
Here you go...

let
Source = 
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY9BDsIwDAT/0nMjre3gOkcgQpw5AVX//w3ikFIqUA6TrBSN15nn4XgK5xxyDsQSh3HgQmxYIRUAIyAlMeY01cdl/FWpIOpBPPkk5r1ORl3dQWvVvarAR33eH+FyvQWNvO5cy/1GC5tKBP/J4Z/aLwVYu2ZqmrjPW22cTBOb9GStE3wzofX4soXxtpcX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Website Sku" = _t, #"Component Qty" = _t, #"Component Weight (Lbs)" = _t, #"Component Height (in)" = _t, #"Component Length (in)" = _t, #"Component Width (in)" = _t, UPC = _t, #"Box Count " = _t]),
#"Changed Type" = 
Table.TransformColumnTypes(
Source,
{{"Website Sku", type text}, {"Component Qty", Int64.Type}, {"Component Weight (Lbs)", Int64.Type}, {"Component Height (in)", Int64.Type}, {"Component Length (in)", Int64.Type}, {"Component Width (in)", Int64.Type}, {"UPC", type text}, {"Box Count ", Int64.Type}}),
addAdditionalRowColumn = 
Table.AddColumn(
#"Changed Type", 
"addRowColumn", 
each if [Component Qty] > 1 
then "1" & Text.Repeat("-1", [Component Qty] - 1) 
else [Component Qty]
),
addRows = 
Table.ExpandListColumn(
Table.TransformColumns(
Table.TransformColumnTypes(
addAdditionalRowColumn, 
{{"addRowColumn", type text}}, "en-US"
), 
{{"addRowColumn", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}
), 
"addRowColumn"
),
#"Changed Type1" = 
Table.TransformColumnTypes(
addRows,
{{"addRowColumn", Int64.Type}}
),
#"Removed Columns" = 
Table.RemoveColumns(
#"Changed Type1",
{"Component Qty"}
),
#"Reordered Columns" = 
Table.ReorderColumns(
#"Removed Columns",
{"Website Sku", "Box Count ", "UPC", "Component Weight (Lbs)", "Component Height (in)", "Component Length (in)", "Component Width (in)", "addRowColumn"}
),
#"Unpivoted Other Columns" = 
Table.UnpivotOtherColumns(
#"Reordered Columns", 
{"Website Sku", "Box Count "}, 
"Attribute", 
"Value"
),
#"Grouped Rows" = 
Table.Group(
#"Unpivoted Other Columns", 
{"Website Sku", "Box Count "}, 
{{"groupedSKUs", each _, type table [Website Sku=nullable text, #"Box Count "=nullable number, Attribute=text, Value=number]}}
),
transformTable = 
Table.AddColumn(
#"Grouped Rows", 
"selectedColumns", 
each Table.PromoteHeaders(
Table.Transpose(
Table.SelectRows(
Table.SelectColumns(
[groupedSKUs], 
{"Attribute", "Value"}
), 
each [Attribute] <> "addRowColumn"
)
)
)
),
#"Removed Columns1" = 
Table.RemoveColumns(
transformTable,
{"groupedSKUs"}
),
getListOfColumnNames = 
Table.ColumnNames(
Table.Combine(
#"Removed Columns1"[selectedColumns]
)
),
#"Converted to Table" = 
Table.FromList(
getListOfColumnNames, 
Splitter.SplitByNothing(), 
null, 
null, 
ExtraValues.Error
),
extractTextAfterDelimiter = 
Table.AddColumn(
#"Converted to Table", 
"Text After Delimiter", 
each Text.AfterDelimiter([Column1], "_"), 
type text
),
determineUPCNumber = 
Table.AddColumn(
extractTextAfterDelimiter, 
"Custom", 
each if Number.Mod(Number.From([Text After Delimiter]),5) = 0 
then Number.IntegerDivide(Number.From([Text After Delimiter]),5) - 1 
else Number.IntegerDivide(Number.From([Text After Delimiter]),5)
),
constructNewName = 
Table.AddColumn(
determineUPCNumber, 
"newName", 
each if [Custom] = null 
then Text.BeforeDelimiter([Column1], "_") & "_1" 
else Text.BeforeDelimiter([Column1], "_") & "_" & Number.ToText([Custom] + 2)
),
#"Removed Columns2" = 
Table.RemoveColumns(
constructNewName,
{"Text After Delimiter", "Custom"}
),
createRenamingList = 
List.Zip(
{#"Removed Columns2"[Column1], #"Removed Columns2"[newName]}
),
expandTransformedTable = 
Table.ExpandTableColumn(
#"Removed Columns1", 
"selectedColumns", 
getListOfColumnNames
),
#"Renamed Columns" = 
Table.RenameColumns(expandTransformedTable, createRenamingList)
in
#"Renamed Columns" 

[jboschee]

@jgeddes Thanks!  Dumb question.  Where do I go to put this code in when I'm in the power query?

[jgeddes · Accepted solution]

You can open the 'Advanced Editor' to get to the code.

The code I provided contains the data source I manually entered. You will need put your source in place of that. As long as your source has the same column names as the example you provided you can just change the source line. If your actual table has different column names you will need to change the code to reflect the actual names.