Forum Discussion
Solved
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: Dat...
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".
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?
