Forum Discussion
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-id | quantity3 | content4-id | quantity4 |
| abcd1 | xyC1-1 | 10 | xyC2-1 | 1 | xyC3-1 | 12 | xyC4-1 | 11 |
| abcd2 | xyC1-2 | 12 | xyC2-2 | 1 | xyC3-2 | 12 | xyC4-2 | 11 |
| abcd3 | xyC1-3 | 14 | xyC2-3 | 1 | xyC3-3 | 3 | xyC4-3 | 11 |
| abcd4 | xyC1-4 | 14 | xyC2-4 | 1 | xyC3-4 | 12 | xyC4-4 | 11 |
| abcd5 | xyC1-5 | 14 | xyC2-5 | 1 | xyC3-5 | 12 | xyC4-5 | 11 |
| abcd6 | xyC1-6 | 14 | xyC2-6 | 1 | xyC3-6 | 3 | xyC4-6 | 11 |
| abcd7 | xyC1-7 | 14 | xyC2-7 | 1 | xyC3-7 | 12 | xyC4-7 | 11 |
| abcd8 | xyC1-8 | 14 | xyC2-8 | 1 | xyC3-8 | 12 | xyC4-8 | 11 |
| abcd9 | xyC1-9 | 14 | xyC2-9 | 1 | xyC3-9 | 3 | xyC4-9 | 11 |
| abcd10 | xyC1-10 | 14 | xyC2-10 | 1 | xyC3-10 | 12 | xyC4-10 | 11 |
| abcd11 | xyC1-11 | 14 | xyC2-11 | 1 | xyC3-11 | 12 | xyC4-11 | 11 |
I would like to have the following result:
| contentAll | QuantityAll |
| xyC1-1 | 10 |
| xyC1-2 | 12 |
| xyC1-3 | 14 |
| xyC1-4 | 14 |
| xyC1-5 | 14 |
| xyC1-6 | 14 |
| xyC1-7 | 14 |
| xyC1-8 | 14 |
| xyC1-9 | 14 |
| xyC1-10 | 14 |
| xyC1-11 | 14 |
| xyC1-12 | 14 |
| xyC2-1 | 1 |
| xyC2-2 | 1 |
| xyC2-3 | 1 |
| xyC2-4 | 1 |
| xyC2-5 | 1 |
| xyC2-6 | 1 |
| xyC2-7 | 1 |
| xyC2-8 | 1 |
| xyC2-9 | 1 |
| xyC2-10 | 1 |
| xyC2-11 | 1 |
| xyC2-12 | 1 |
| xyC3-1 | 12 |
| xyC3-2 | 12 |
| xyC3-3 | 3 |
| xyC3-4 | 12 |
| xyC3-5 | 12 |
| xyC3-6 | 3 |
| xyC3-7 | 12 |
| xyC3-8 | 12 |
| xyC3-9 | 3 |
| xyC3-10 | 12 |
| xyC3-11 | 12 |
I would appreciate all the help you can give me. Thank you!
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
17 Replies
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,
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
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,
AndrazOk 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,
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
