Forum Discussion
How to append multiple columns from inside one table
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
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,
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
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,
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,
AndrazOk, if the data is not private then send me that and I'll check it.
Regards
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/EueJyvbjKrFFt5RzW5wvobIBlhSu0GyODYP14vXo_INRMw?e=Ai8ZE6
Thank you for the help!
Andraz
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 ID Artisan 1 Artisan 2 Artisan 3 1 Bradley James 2 Mike Bradley John
I would like to create a table like this (excluding blanks as shown below):
Job Card ID Artisan 1 Bradley 1 James 2 Mike 2 Bradley 2 John 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
