Merge multiple columns into one column

[apoje]

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!

[v-frfei-msft]

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.

[apoje]

HI @v-frfei-msft,

the example data is in the table not in the PQ. With some deleted rows. I am would like to append those 8 Columns into 2. And than preform a group by function in PQ. 

Hopefully you can help. 

https://ipotechnik-my.sharepoint.com/:x:/g/personal/andraz_poje_ipotechnik_de/ES51pnTEvUJMht_Vq7niJH...

Thank you!

Andraz

[Anonymous]

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"