question

Kaouw-1348 avatar image
0 Votes"
Kaouw-1348 asked Ehren answered

Dynamic columns and dependent values

Overview
- I have a data table [Data] displaying stock types, locations, bins, categories and available stock
- I have a stock position list [Storages] displaying different types, locations, bins and categories that I want to get the sum of
- Using [Data], I want to add one column for each [Storages]-row and I want it to sum according to the criteria set in multiple columns (Type, Location, Bin etc)

How to combine the data?
1. Start with the [Data] table
2. Add [Storages] and transpose its rows into columns (perhaps like [Transposed] is done below?)
3. For each of these columns I want to get [Available stock] if the row meets all criteria (Type, Location, Bin etc)

My challenges:
Types, Locations, Bins etc could be either:
1. An interval (from 1 to 99)
2. A number (305)
3. Blank (null)
So for each row, I want the [Available stock] if it meets all of the criteria in these fields.
Also worth mentioning, [Storages] is a table that will change. That's why I can't hardcode the columns.

Actions
- I created [Transposed] which is an empty table and it's columns are transposed from the [Storages] rows
- I thought I could use it by adding it as a query to a list of all material rows? But I'm getting stuck here, since I want to sum each of these columns.

Question
- How would this summarization be possible?
- Is there a possibility to write a function that appends all of these variables?

At the bottom I have added a table displaying how the end result should be.

[Data]:
144965-mquery-data.png

 let
     Source = #table({"Material",    "Storage Type",    "Storage Location",    "Storage Bin", "Available stock", "Category"},
     {
         {"A-100", 1, 1, "D1", 10, ""},
         {"A-100", 50, 1, "D2",  20, ""},
         {"A-100", 304, 1, "D1", 10, ""},
         {"A-100", 301, 1, "A1", 20, ""},
         {"A-100", 301, 1, "A4", 10, "S"},
         {"A-100", 301, 1, "A6", 30, ""},
         {"A-100", 301, 1, "B0", 30, "S"},
         {"A-100", 301, 1, "B2", 40, ""},
         {"A-100", 301, 1, "C3", 10, ""},
         {"A-100", 301, 1, "C6", 30, "S"},
    
         {"B-100", 1, 1, "D0", 30, ""},
         {"B-100", 50, 1, "D3",  10, ""},
         {"B-100", 304, 1, "D4", 40, ""},
         {"B-100", 301, 1, "A0", 50, ""},
         {"B-100", 301, 1, "A0", 70, "S"},
         {"B-100", 301, 1, "A2", 20, ""},
         {"B-100", 301, 1, "B1", 40, "S"},
         {"B-100", 301, 1, "B4", 60, ""},
         {"B-100", 301, 1, "C1", 30, ""},
         {"B-100", 301, 1, "C9", 10, "S"}
     }),
     #"Changed type" = Table.TransformColumnTypes(Source,{
                 {"Storage Type", Int64.Type}, {"Storage Location", Int64.Type}, {"Available stock", Int64.Type}, {"Storage Bin", type text}})
 in
     #"Changed type"

[Storages]:
145002-mquery-storages.png

 let
     Source = #table({"Description", "Type From", "Type To", "Loc From", "Loc To", "Bin From", "Bin To", "Category"},
     {
         {"Type 1-100", "1", "99", "", "", "", "", ""},
         {"Type 304", "304", "", "", "", "", "", ""},
         {"Type 301 Bin A", "301", "", "", "", "A*", "", ""},
         {"Type 301 Bin B", "301", "", "", "", "B*", "", ""},
         {"Type 301 Bin C", "301", "", "", "", "C*", "", ""},
         {"Category S", "", "", "", "", "", "", "S"}
     })
 in        
 Source

[Transposed]:
145013-mquery-transposed.png

 let
     Source = Storages,
     Transposed = Table.Transpose(Source),
     Headers = Table.PromoteHeaders(Transposed, [PromoteAllScalars=true]),
     DeleteRows = Table.Skip(Headers, Table.RowCount(Headers)),
     ChangeTypes = Table.TransformColumnTypes(DeleteRows,
         List.Transform(
             Table.ColumnNames(DeleteRows),
             each {_, Int64.Type}
         )
     )
 in
     ChangeTypes

End result should look like this:
144964-mquery-storage-type-bin-etc.png



power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Ehren avatar image
0 Votes"
Ehren answered

Power Query is no longer supported here on Q&A. Please post your question over on https://aka.ms/PQCommunity.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.