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 

apoje's avatar
apoje
Helper II
6 years ago

Sorry ibarrau I'm too stupid to understand ğŸ˜•

 

the below code is already in the editor:

#"Inserted Multiplication" = Table.AddColumn(#"Changed Type1", "Multiplication", each List.Product({[Quantity], [#"content-1"]}), Int64.Type)

 

so what exactly do I need to change?

 

Thanks!

Andraz

ibarrau's avatar
ibarrau
Super User
6 years ago

Please don't think that, you might be starting and I may not always make myself clear 🙂

I'm talking about this:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-types

A change of data looks like this:

= Table.TransformColumnTypes(#"Last Step",{{"Column1", Int64.Type}})

Then about errors you can check this:

https://youtu.be/drrh6ciVvXY?t=99 

Try that on multiplication.1 column so we can check what is this about.

Regards,

 

  • AlB's avatar
    AlB
    Community Champion
    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 

  • apoje's avatar
    apoje
    Helper II
    6 years ago

    Hey ibarrau, 

     

    thanks for all your help I really appreciate it. 

    I think I understand what you meant - but looking at the table from where the error should originate - there is none. 

     

    I have no idea where to go from here. I even manually checked the data (because there is not many rows) - I still do not find it.

     

    Can I send you the workbook somewhere if you can check it out? 

     

    Regards,
    Andraz

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    Ok, if the data is not private then send me that and I'll check it.

    Regards

  • apoje's avatar
    apoje
    Helper II
    6 years ago

    Hi AlB 

     

    I have to say that I did check it but didn't understand the code fully so I went with the other proposed solution since I could follow that along. 

     

    I get "most" of the code now 🙂 and I was able to amend it for it to work ğŸ’ª

     

    Thanks to both of you AlB ibarrau !

  • LiziM's avatar
    LiziM
    Frequent Visitor
    4 years ago

    SOLVED:

    https://community.powerbi.com/t5/Desktop/Appending-columns/m-p/1555845#M635475

    Hi AIB,

     

    I have a similar problem, which I've tried to apply your solutions to, but I'm having some issues still.

     

    Currently, I have a table like this:

    Job Card IDArtisan 1Artisan 2Artisan 3
    1Bradley James 
    2MikeBradley

    John

    I would like to create a table like this (excluding blanks as shown below):

    Job Card IDArtisan
    1Bradley
    1James
    2Mike
    2Bradley
    2John

    So far, I am able to get all the artisans into one column, but I am unsure on how to get the corresponding Job Card ID.

     

    Any help would be much appreciated and I'll be sure to let you know if the solution does/doesn't work!

    SOLVED:

    https://community.powerbi.com/t5/Desktop/Appending-columns/m-p/1555845#M635475