Forum Discussion
Appending selected columns from a different Query
HI, I have a dataset which reflects bundle content. So, the SKU is braked down to multiple endSKUs and their quantity within a bundle.
The thing is that I need the endSKUs in a structured table: Date | SKU | Quantity
Currently the bundleSKU table looks like this:
Date | SKU | Quantity | SKU-1 | Q-1 | SKU-2 | Q-2 | SKU-3 | Q-3 | SKU-4 | Q-4 | SKU-5 | Q-5 ...
The appended table in a new query should look like this:
Date | SKU | Quantity
data from : {date | sku-1 | q-1}
.
.
.
.
data from {date | sku-2 | q-2}
.
.
.
etc.
When I get a query in this form will filter out the null values group by the date and SKU to get the corresponding sums and that should be it…
Hopefully that can be done in a more efficient way than copy the queries, delete all the rows you do not need and then append all the queries together… I would like something more dynamic and repeatable 😊
Thank you!
Andraž
Here is one possible implementation
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VdZcsIwDL1LvikjS3IS//abGzDc/xq1JW/CzNBCkrYzMOgpy9Nucb1O7uzgjIBuOk2flw8AF6J0h4gx/vj4ffdzO12n+XjKwUuknlIQEIc9vdyfku8pAXtKReQgKx3RHH+SFmAttzp2S7Yp3rAULS6dGPJjDtfysh/E3i29VYJghn0r7HhKMOlW9FoQX0k8G3JBOPtGnjTZJMriq+TLQD4bckFUw/E0CF0tvhcDk3NB4Gk92gxnelAQkEtm7Nb2CD2lICC5vBWlv6cklyPYEJCUloybXdwkc2wIildhQzfX4ykHL9kkU1C8umUyhwHNZnQJiucV/2/KMbC7Uw7l8xteminIevL5ROe3aswhmfWgaShOoTTyUMVQRemdLJYRArp2qFZGiW4rXF4btdDEFEVukWS9l/IbosgiPozOn7V0iOli8qiIsS1tWE78jXN5EO9woBzEO1TEaia8IIyTvi5U3cLUrVGbuR3MtFfUu72W0tvY7WC2FEUe6j8FD4U3ithEamINEIfSP1Es/aPFrxK3h5aqLFSljR5baeanIg7Y6JLmvVH6cG4HU4yKyNV+f7ZKdhn8thm3Lw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Invoice Date" = _t, #"Item nr." = _t, Quantity = _t, tblBundle.SKU = _t, #"tblBundle.var-nr-1" = _t, #"tblBundle.content-1" = _t, #"tblBundle.var-nr-2" = _t, #"tblBundle.content-2" = _t, #"tblBundle.var-nr-3" = _t, #"tblBundle.content-3" = _t, #"tblBundle.var-nr-4" = _t, #"tblBundle.content-4" = _t, #"tblBundle.var-nr-5" = _t, #"tblBundle.content-5" = _t, #"tblBundle.var-nr-6" = _t, #"tblBundle.content-6" = _t, #"tblBundle.var-nr-7" = _t, #"tblBundle.content-7" = _t, #"tblBundle.var-nr-8" = _t, #"tblBundle.content-8" = _t, #"tblBundle.var-nr-9" = _t, #"tblBundle.content-9" = _t, #"tblBundle.var-nr-10" = _t, #"tblBundle.content-10" = _t, #"tblBundle.var-nr-11" = _t, #"tblBundle.content-11" = _t, #"tblBundle.var-nr-12" = _t, #"tblBundle.content-12" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Invoice Date", "Item nr.", "Quantity", "tblBundle.SKU"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Invoice Date", "Attribute", "Value"}), #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",each [Attribute],each Text.Start([Attribute],11) ,Replacer.ReplaceValue,{"Attribute"}), #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type), #"Replaced Value1" = Table.ReplaceValue(#"Added Index",each [Index],each if Number.IsOdd([Index]) then [Index]-1 else [Index],Replacer.ReplaceValue,{"Index"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute]), "Attribute", "Value"), #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}), #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"tblBundle.v", "tblBundle.var"}, {"tblBundle.c", "tblBundle.content"}}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns",{"Invoice Date", "tblBundle.var", "tblBundle.content"}) in #"Removed Other Columns1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
10 Replies
Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.
hi lbendlin I am sending the link to the file below.
The sample query is "order-Bundle" which is merged from 2 tables: Data and Bundle
the desired result is shown in the tab: Desired output.
I would really appreciate the help!
Thank you!
Andraž
Here is one possible implementation
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VdZcsIwDL1LvikjS3IS//abGzDc/xq1JW/CzNBCkrYzMOgpy9Nucb1O7uzgjIBuOk2flw8AF6J0h4gx/vj4ffdzO12n+XjKwUuknlIQEIc9vdyfku8pAXtKReQgKx3RHH+SFmAttzp2S7Yp3rAULS6dGPJjDtfysh/E3i29VYJghn0r7HhKMOlW9FoQX0k8G3JBOPtGnjTZJMriq+TLQD4bckFUw/E0CF0tvhcDk3NB4Gk92gxnelAQkEtm7Nb2CD2lICC5vBWlv6cklyPYEJCUloybXdwkc2wIildhQzfX4ykHL9kkU1C8umUyhwHNZnQJiucV/2/KMbC7Uw7l8xteminIevL5ROe3aswhmfWgaShOoTTyUMVQRemdLJYRArp2qFZGiW4rXF4btdDEFEVukWS9l/IbosgiPozOn7V0iOli8qiIsS1tWE78jXN5EO9woBzEO1TEaia8IIyTvi5U3cLUrVGbuR3MtFfUu72W0tvY7WC2FEUe6j8FD4U3ithEamINEIfSP1Es/aPFrxK3h5aqLFSljR5baeanIg7Y6JLmvVH6cG4HU4yKyNV+f7ZKdhn8thm3Lw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Invoice Date" = _t, #"Item nr." = _t, Quantity = _t, tblBundle.SKU = _t, #"tblBundle.var-nr-1" = _t, #"tblBundle.content-1" = _t, #"tblBundle.var-nr-2" = _t, #"tblBundle.content-2" = _t, #"tblBundle.var-nr-3" = _t, #"tblBundle.content-3" = _t, #"tblBundle.var-nr-4" = _t, #"tblBundle.content-4" = _t, #"tblBundle.var-nr-5" = _t, #"tblBundle.content-5" = _t, #"tblBundle.var-nr-6" = _t, #"tblBundle.content-6" = _t, #"tblBundle.var-nr-7" = _t, #"tblBundle.content-7" = _t, #"tblBundle.var-nr-8" = _t, #"tblBundle.content-8" = _t, #"tblBundle.var-nr-9" = _t, #"tblBundle.content-9" = _t, #"tblBundle.var-nr-10" = _t, #"tblBundle.content-10" = _t, #"tblBundle.var-nr-11" = _t, #"tblBundle.content-11" = _t, #"tblBundle.var-nr-12" = _t, #"tblBundle.content-12" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Invoice Date", "Item nr.", "Quantity", "tblBundle.SKU"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Invoice Date", "Attribute", "Value"}), #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",each [Attribute],each Text.Start([Attribute],11) ,Replacer.ReplaceValue,{"Attribute"}), #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type), #"Replaced Value1" = Table.ReplaceValue(#"Added Index",each [Index],each if Number.IsOdd([Index]) then [Index]-1 else [Index],Replacer.ReplaceValue,{"Index"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute]), "Attribute", "Value"), #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}), #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"tblBundle.v", "tblBundle.var"}, {"tblBundle.c", "tblBundle.content"}}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns",{"Invoice Date", "tblBundle.var", "tblBundle.content"}) in #"Removed Other Columns1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
HI! Thank you! I am still struggling a bit since this is ways out of my level. I replaced the "json code" with my querry, and changed the names of the columns of the actual data, but now I am getting the error message:
Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]let Source = Table.FromRows(tblHRbundles, let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Datum računa" = _t, #"Šifra Artikla" = _t, Količina = _t, #"var-nr-1" = _t, #"content-1" = _t, #"var-nr-2" = _t, #"content-2" = _t, #"var-nr-3" = _t, #"content-3" = _t, #"var-nr-4" = _t, #"content-4" = _t, #"var-nr-5" = _t, #"content-5" = _t, #"var-nr-6" = _t, #"content-6" = _t, #"var-nr-7" = _t, #"content-7" = _t, #"var-nr-8" = _t, #"content-8" = _t, #"var-nr-9" = _t, #"content-9" = _t, #"var-nr-10" = _t, #"content-10" = _t, #"var-nr-11" = _t, #"content-11" = _t, #"var-nr-12" = _t, #"content-12" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Datum računa", "Šifra Artikla", "Količina"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Datum računa", "Attribute", "Value"}), #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",each [Attribute],each Text.Start([Attribute],11) ,Replacer.ReplaceValue,{"Attribute"}), #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type), #"Replaced Value1" = Table.ReplaceValue(#"Added Index",each [Index],each if Number.IsOdd([Index]) then [Index]-1 else [Index],Replacer.ReplaceValue,{"Index"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute]), "Attribute", "Value"), #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}), #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"v", "var"}, {"c", "content"}}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns",{"Datum računa", "var", "content"}) in #"Removed Other Columns1"Can you please have a look what am I missing?
Thank you!
Andraž
Can't help you more if I can't see your new sample data.
the data is the same (longer) but same only the column names are renamed to suit the actual dataset.
I am using the code below:
let Source = Table.FromRows(tblHRbundles, let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Datum računa" = _t, #"Šifra Artikla" = _t, Količina = _t, #"var-nr-1" = _t, #"content-1" = _t, #"var-nr-2" = _t, #"content-2" = _t, #"var-nr-3" = _t, #"content-3" = _t, #"var-nr-4" = _t, #"content-4" = _t, #"var-nr-5" = _t, #"content-5" = _t, #"var-nr-6" = _t, #"content-6" = _t, #"var-nr-7" = _t, #"content-7" = _t, #"var-nr-8" = _t, #"content-8" = _t, #"var-nr-9" = _t, #"content-9" = _t, #"var-nr-10" = _t, #"content-10" = _t, #"var-nr-11" = _t, #"content-11" = _t, #"var-nr-12" = _t, #"content-12" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Datum računa", "Šifra Artikla", "Količina"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Datum računa", "Attribute", "Value"}), #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",each [Attribute],each Text.Start([Attribute],1) ,Replacer.ReplaceValue,{"Attribute"}), #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type), #"Replaced Value1" = Table.ReplaceValue(#"Added Index",each [Index],each if Number.IsOdd([Index]) then [Index]-1 else [Index],Replacer.ReplaceValue,{"Index"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute]), "Attribute", "Value"), #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}), #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"v", "var"}, {"c", "content"}}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns",{"Datum računa", "var", "content"}) in #"Removed Other Columns1"is the "source step" correct where I replaced the json part of the code?
Likely not. Change it to
let
Source = tblHRBundles,
