Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
apoje
Helper II
Helper II

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-idcontent1-idquantity1content2-idquantity2content3-idquantity3content4-idquantity4
abcd1xyC1-110xyC2-11xyC3-112xyC4-111
abcd2xyC1-212xyC2-21xyC3-212xyC4-211
abcd3xyC1-314xyC2-31xyC3-33xyC4-311
abcd4xyC1-414xyC2-41xyC3-412xyC4-411
abcd5xyC1-514xyC2-51xyC3-512xyC4-511
abcd6xyC1-614xyC2-61xyC3-63xyC4-611
abcd7xyC1-714xyC2-71xyC3-712xyC4-711
abcd8xyC1-814xyC2-81xyC3-812xyC4-811
abcd9xyC1-914xyC2-91xyC3-93xyC4-911
abcd10xyC1-1014xyC2-101xyC3-1012xyC4-1011
abcd11xyC1-1114xyC2-111xyC3-1112xyC4-1111

 

I would like to have the following result:

 
contentAllQuantityAll
xyC1-110
xyC1-212
xyC1-314
xyC1-414
xyC1-514
xyC1-614
xyC1-714
xyC1-814
xyC1-914
xyC1-1014
xyC1-1114
xyC1-1214
xyC2-11
xyC2-21
xyC2-31
xyC2-41
xyC2-51
xyC2-61
xyC2-71
xyC2-81
xyC2-91
xyC2-101
xyC2-111
xyC2-121
xyC3-112
xyC3-212
xyC3-33
xyC3-412
xyC3-512
xyC3-63
xyC3-712
xyC3-812
xyC3-93
xyC3-1012
xyC3-1112

 

 

I would appreciate all the help you can give me. Thank you!

Andraz

1 ACCEPTED SOLUTION

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 

SU18_powerbi_badge

View solution in original post

17 REPLIES 17
ziying35
Impactful Individual
Impactful Individual

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
ibarrau
Super User
Super User

Hi! i don't thinks this is just column. Your idea is whole new table. You can add a new step on your power query that will transform the complete table or create a blank query to query your table creating a second one with the result. Let's assume your table name is Table1. Now you can create a new blank query with a code that should be like this:

= Table.Combine({
    Table.RenameColumns(
        Table.SelectColumns(Table1, "idcontent1", "idquantity1")
        ,{{"idcontent1", "idcontent"},{"idquantity1", "idquantity"} })
    ,
    Table.RenameColumns(
        Table.SelectColumns(Table1, "idcontent2", "idquantity2")
        ,{{"idcontent2", "idcontent"},{"idquantity2", "idquantity"} })
    ,
    Table.RenameColumns(
        Table.SelectColumns(Table1, "idcontentN", "idquantityN")
        ,{{"idcontentN", "idcontent"},{"idquantityN", "idquantity"} })
})

 Table.Combine is for append. Table.RenameColumns to append the same columns together. Table.SelectColumns is to just select the columns you will use in the append.

 

Hope this helps,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

hi @ibarrau  you are right my phrasing was off. The result would actually be a new table. 

 

 

= Table.Combine({
    Table.RenameColumns(
        Table.SelectColumns(tblSL, "var-nr-1", "Multiplication.1")
        ,{{"var-nr-1", "var-nr"},{"Multiplication.1", "Quantity"} })
    ,
    Table.RenameColumns(
        Table.SelectColumns(tblSL, "var-nr-2", "Multiplication.2")
        ,{{"var-nr-2", "var-nr"},{"Multiplication.2", "Quantity"} })
    ,
    Table.RenameColumns(
        Table.SelectColumns(tblSL, "var-nr-3", "Multiplication.3")
        ,{{"var-nr-3", "var-nr"},{"Multiplication.3", "Quantity"} })
    ,
    Table.RenameColumns(
        Table.SelectColumns(tblSL, "var-nr-4", "Multiplication.4")
        ,{{"var-nr-4", "var-nr"},{"Multiplication.4", "Quantity"} })
})

 

 

I have created a blank query and written the code as you suggested. However I am getting an error message

Expression.Error: We cannot convert the value "Multiplication.1" to type Number.
Details:
Value=Multiplication.1
Type=[Type]

 

All the Multiplication columns are a whole number type. I am not sure how to amend the error...

 

Regards,
Andraz


Ok good. The error doesn't look that is coming from that code. It says it's trying to convert multiplication.1 to number and it can't do it. Check your previous o next steps and check if you have a convert to number somewhere. You might have wrong data in the column that is not allowing you to convert it or apply math over the column.

You can always check my code by parts. Starting without the combine to see if it works:

    Table.RenameColumns(
        Table.SelectColumns(tblSL, "var-nr-1", "Multiplication.1")
        ,{{"var-nr-1", "var-nr"},{"Multiplication.1", "Quantity"} })

If you want you can share previous steps so we can help you find the error.

 

Regards, 


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Hi @ibarrau 

 

I have checked once again and I do not see any overlaps in the data type. 

 

here is the link of my workbook:

https://ipotechnik-my.sharepoint.com/:x:/g/personal/andraz_poje_ipotechnik_de/EW_Y-gkPoyZHq8jBg--zRB...

 

I appreciate your help!

 

Regards,

Andraz
Andraz

Is the error from a specific step? or is it showing that when close & apply?

The sharepoint link doesn't show me anything. Just an excel with A1 = ExternealData_1.

 

Can you show the "advanced editor" power query code? that way we can check whats is in the query besides what I have sent you.

 

Regards,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Hi @ibarrau I have shared the flie where all the data is only loaded with "connection only". So there are no tables loaded. Maybe if you go under Queries&Connections you'll se it...

 

The blank query only has your code nothing else and that results in the error message I have sent you.

 

The code of the connected table is:

let
    Source = Excel.Workbook(File.Contents("C:\...\fba-pošiljanje-goods-in-v01.xlsx"), null, true),
    tblSLO_FBA_Table = Source{[Item="tblSLO_FBA",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(tblSLO_FBA_Table,{{"Date", type date}, {"Invoice number", type text}, {"SKU", type text}, {" Shipment ID", type any}, {"desc", type text}, {"qty ordered", Int64.Type}, {"Put on invoice (x - for YES, DONE - for DONE)", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Put on invoice (x - for YES, DONE - for DONE)"] = "x")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"SKU"}, {{"Quantity", each List.Sum([qty ordered]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"SKU"}, tbl_bundle, {"SKU-amz"}, "tbl_bundle", JoinKind.LeftOuter),
    #"Expanded tbl_bundle" = Table.ExpandTableColumn(#"Merged Queries", "tbl_bundle", {"var-nr-1", "content-1", "var-nr-2", "content-2", "var-nr-3", "content-3", "var-nr-4", "content-4"}, {"var-nr-1", "content-1", "var-nr-2", "content-2", "var-nr-3", "content-3", "var-nr-4", "content-4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded tbl_bundle",{{"content-1", Int64.Type}, {"Quantity", Int64.Type}, {"content-2", Int64.Type}, {"content-3", Int64.Type}, {"content-4", Int64.Type}}),
    #"Inserted Multiplication" = Table.AddColumn(#"Changed Type1", "Multiplication", each List.Product({[Quantity], [#"content-1"]}), Int64.Type),
    #"Inserted Multiplication1" = Table.AddColumn(#"Inserted Multiplication", "Multiplication.1", each List.Product({[Quantity], [#"content-2"]}), Int64.Type),
    #"Inserted Multiplication2" = Table.AddColumn(#"Inserted Multiplication1", "Multiplication.2", each List.Product({[Quantity], [#"content-3"]}), Int64.Type),
    #"Inserted Multiplication3" = Table.AddColumn(#"Inserted Multiplication2", "Multiplication.3", each List.Product({[Quantity], [#"content-4"]}), Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted Multiplication3",{"SKU", "Quantity", "var-nr-1", "content-1", "Multiplication", "var-nr-2", "content-2", "Multiplication.1", "var-nr-3", "content-3", "Multiplication.2", "var-nr-4", "content-4", "Multiplication.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Multiplication.3", "Multiplication.4"}, {"Multiplication.2", "Multiplication.3"}, {"Multiplication.1", "Multiplication.2"}, {"Multiplication", "Multiplication.1"}})
in
    #"Renamed Columns"

 

There is one more querry connected where I am doing the outer join from:

 

let
    Source = Excel.Workbook(File.Contents("C:\...\dAMZ-BundleContent-v02.xlsx"), null, true),
    tbl_bundle_Table = Source{[Item="tbl_bundle",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(tbl_bundle_Table,{{"SKU-amz", type text}, {"ASIN", type text}, {"title-amz", type text}, {"var-nr-1", type text}, {"content-1", Int64.Type}, {"var-nr-2", type text}, {"content-2", Int64.Type}, {"var-nr-3", type text}, {"content-3", Int64.Type}, {"var-nr-4", type text}, {"content-4", Int64.Type}, {"Fulfillment-Channel", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ASIN", "Fulfillment-Channel"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"var-nr-1", "content-1", "var-nr-2", "content-2", "var-nr-3", "content-3", "var-nr-4", "content-4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"var-nr-2", type text}, {"var-nr-1", type text}, {"var-nr-3", type text}, {"var-nr-4", type text}, {"content-1", Int64.Type}, {"content-2", Int64.Type}, {"content-3", Int64.Type}, {"content-4", Int64.Type}})
in
    #"Changed Type1"

 

that is all that is connected to.

Andraz

 

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

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

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,

 


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

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

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

Regards


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

HI @ibarrau I created a folder on OneDrive and amended the data. 

 

The file where is the code is: invoice-v01

 

the folder link: https://ipotechnik-my.sharepoint.com/:f:/g/personal/andraz_poje_ipotechnik_de/EueJyvbjKrFFt5RzW5wvob...

 

Thank you for the help!

Andraz

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 

SU18_powerbi_badge

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

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 !

Hi @apoje,

copy this M code in a blank query to see the steps starting from your initial example

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdJLCgMxDAPQu2Q9hdhxfuseY5hFP4dob98Go9aaXQTRIwHte7rdH09JW3q9r3JZB8me1JOH4kE9mSdJx+aAAtBwSz0BUAKUgQJgHcQAlAisUNAv3Df0jfoW+0YPMAYqgEpAjUAloDLQADQCWgRa/EHjfke/U7/HfqcHdAYGgEHAiMAgYDAwAUwCZgRm/MHkPobznVEmweNvSJmXlE/Kf4zCCs/xtEcM8vgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"bundle-id" = _t, #"content1-id" = _t, quantity1 = _t, #"content2-id" = _t, quantity2 = _t, #"content3-id" = _t, quantity3 = _t, #"content4-id" = _t, quantity4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"bundle-id", type text}, {"content1-id", type text}, {"quantity1", Int64.Type}, {"content2-id", type text}, {"quantity2", Int64.Type}, {"content3-id", type text}, {"quantity3", Int64.Type}, {"content4-id", type text}, {"quantity4", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"bundle-id"}),

    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_})
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 

SU18_powerbi_badge

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors