cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
apoje
Helper II
Helper II

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ž

1 ACCEPTED SOLUTION

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".

View solution in original post

10 REPLIES 10
lbendlin
Super User
Super User

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,

 

 

THANKS!!!!

apoje
Helper II
Helper II

hi @lbendlin I am sending the link to the file below. 

 

https://ipotechnik-my.sharepoint.com/:x:/g/personal/andraz_poje_ipotechnik_de/EexaEViYrYRGhsvhirebKP...

 

 

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".

View solution in original post

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ž

change this

    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",each [Attribute],each Text.Start([Attribute],11) ,Replacer.ReplaceValue,{"Attribute"}),

to this

    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",each [Attribute],each Text.Start([Attribute],1) ,Replacer.ReplaceValue,{"Attribute"}),

Hi @lbendlin even after this change it does not work. Same error message...

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors