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 

ibarrau's avatar
ibarrau
Super User
6 years ago

Ok. At the end of your table, try adding a new step changing the type of the columns as you want. Convert all Multiplication.N to number so the new table have the correct data. This code:

 

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

 

This is not changing the column to Int64.Type it is just leting the engine know that you want to have a number but it won't convert it. The real step changing types will show error if you have character that is not a number. You can validate that "Keeping Errors" as the last step of the table. If the step doesn't show rows then delete the step and your dataset is ready to be appended.

 

Regards,

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

  • 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 

  • 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,

     

  • 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