Forum Discussion

Skoo84's avatar
Skoo84
New Member
4 years ago
Solved

Append Column Pairs Below Each Other

I'm working on a sales report based on an excel file.

The orders are coming in a single line with the SKUs, Product descriptions and quantities in a single column each.

 

SKUProduct DescriptionQuantity
SKU1, SKU2, SKU3Desc1, Desc2, Desc31:5:7

SKU8, SKU5

Desc8, Desc56:3

 

I managed to separate the SKUs, Description and Quantities by the delimiters, but I ended up with 23 columns each.

I would like to get all the SKU with the related Description in a single column pair:

SKUDescription
SKU1Desc1
SKU2Desc2

SKU3

Desc3

 

How should I proceed?

General Comment
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    I gave one solution to this here: https://stackoverflow.com/questions/70007878

     

    Here's a variation that does more steps with the GUI:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvYONdRRAJJGYNJYSUfJJbU4GSgGoowgFEjU0MrUylwpVgesxQKs2BSq2AKiCsQ1szJWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, #"Product Description" = _t, Quantity = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({Text.Split([SKU], ", "), Text.Split([Product Description], ", ")})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Other Columns" = Table.SelectColumns(#"Extracted Values",{"Custom"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"SKU", "Description"})
in
    #"Split Column by Delimiter"

    This uses Extract Values and Split by Delimiter instead of Table.FromRows.

1 Reply

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    I gave one solution to this here: https://stackoverflow.com/questions/70007878

     

    Here's a variation that does more steps with the GUI:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvYONdRRAJJGYNJYSUfJJbU4GSgGoowgFEjU0MrUylwpVgesxQKs2BSq2AKiCsQ1szJWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, #"Product Description" = _t, Quantity = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({Text.Split([SKU], ", "), Text.Split([Product Description], ", ")})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Other Columns" = Table.SelectColumns(#"Extracted Values",{"Custom"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"SKU", "Description"})
in
    #"Split Column by Delimiter"

    This uses Extract Values and Split by Delimiter instead of Table.FromRows.