Dynamic columns and dependent values

Question

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]:

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]:

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]:

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:

Answer 1

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