Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jboschee
Frequent Visitor

Trouble with Transpose

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 SkuComponent QtyComponent Weight (Lbs)Component Height (in)Component Length (in)Component Width (in)UPCBox Count                    
AB-CD-DD-123424489008100993822974                   
AB-CD-DD-1234136133222008100993821814                   
AB-CD-DD-123411096008100993826004                   
ZXY-FHR-6424213103008100993811085                   
ZXY-FHR-642411096008100993800265                   
ZXY-FHR-642411993023008100478692835                   
ZXY-FHR-6424161183131008100478693205                   
                           
                           
                           
FORMAT NEEDED                          
vendor_skunumber_of_boxesbox_1_upcshipping_weight1package_height1package_length1package_width1box_2_upcshipping_weight1package_height1package_length1package_width1box_3_upcshipping_weight1package_height1package_length1package_width1box_4_upcshipping_weight1package_height1package_length1package_width1box_5_upcshipping_weight1package_height1package_length1package_width1
AB-CD-DD-123440081009938229744890081009938229744890081009938218136133222008100993826001096     
ZXY-FHR-64245008100993811081310300810099381108131030081009938002610960081004786928319930230081004786932061183131
1 ACCEPTED SOLUTION

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

jgeddes_0-1685646828732.png

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.

View solution in original post

3 REPLIES 3
jgeddes
Super User
Super User

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" 

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

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

jgeddes_0-1685646828732.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.