Forum Discussion

apoje's avatar
apoje
Helper II
6 years ago

Merge multiple columns into one column

I have a dataset which contains what specific items are a part of a bundle, there is also the associated quantity. Bundle has up to 4 components and I have 4 columns that carry variation.id and 4 columns that carry stock of that specific variation. Basically 8 columns in total. I would like to combine those 8 columns into 2 columns where I could than preform a group by function – to get out the unique SKUs and their respective quantity.

 

How could I combine those 8 columns into only 2 (variation.id and stock)?

 

 in the example the var-nr represents variation id, and product represents the stock quantity.

 

Thank you!

Need Help

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi apoje ,

     

    Did you want to union the columns? Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        The code below will get you to the point where you can group by. Hope it helps!

         

        let
    Source = Excel.CurrentWorkbook(){[Name="stock_bundle"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,0,null,Replacer.ReplaceValue,{"var-nr-1", "product-1", "var-nr-2", "product-2", "var-nr-3", "product-3", "var-nr-4", "product-4"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {}, "Attribute", "Product"),
    AddVar = Table.AddColumn(#"Unpivoted Other Columns", "Var", each if Text.Start([Attribute],6) = "var-nr" then [Product] else null),
    #"Filled Down" = Table.FillDown(AddVar,{"Var"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each Text.StartsWith([Attribute], "product"))
in
    #"Filtered Rows"