Forum Discussion

apoje's avatar
apoje
Helper II
6 years ago
Solved

How to append multiple columns from inside one table

Hi I would like to append multiple columns from one table into one column in PowerQuery. I have a data looking like this:   bundle-id content1-id quantity1 content2-id quantity2 content3-i...
Need Help
  • AlB's avatar
    AlB
    6 years ago

    Hi apoje 

    I am not sure why you've completely ignored my solution all along 🤔. It does exactly what you showed in your first post in a simple way. If the base table is "tbl_bundle" in the files you've shared, you can create a blank query with the following M code and you'll get the result. It is the same as I posted a couple of days ago already:  

    let
    Source = tbl_bundle,
    #"Removed Columns" = Table.RemoveColumns(Source,{"SKU-bundleID"}),
    ExtractColumns_ = Table.ToColumns(#"Removed Columns"),
    ContentCols_ = List.Combine(List.Alternate(ExtractColumns_,1,1,1)),
    QuantityCols_ = List.Combine(List.Alternate(ExtractColumns_,1,1,0)),
    final_ = Table.FromColumns({ContentCols_, QuantityCols_}, {"ContentAll", "QuantityAll"})
in
    final_

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

ziying35's avatar
ziying35
Impactful Individual
6 years ago

Hi, apoje 

my code as below:

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_bundle"]}[Content],
    trans = Table.Combine(List.Transform(List.Split(List.Skip(Table.ToColumns(Source)),2),each Table.FromColumns(_,{"ContentAll","QuantityAll"}))),
    filter = Table.SelectRows(trans, each ([QuantityAll] <> null and [QuantityAll] <> 0)),
    chtype = Table.TransformColumnTypes(filter,{{"ContentAll", Text.Type}})
in
    chtype