尝试将所有字段乘以使用量列

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Names  = List.RemoveFirstN(Table.ColumnNames(#"Source"),2),
Transform = Table.FromRecords(Table.TransformRows(Source, (x)=>Record.TransformFields(x, List.Transform( Names, (y) => { y, each _ * x[Usage Quantity] } ) ) ))
in Transform

好吧，所以我在PQ for Mac上做了这个，这有它固有的问题。也就是说，看看这是否解决了你的问题：
1.首先，取消透视物料编号和使用数量。这将产生类似这样的结果：

+-------------+-----------+-----------+-------+
    | item number | usage qty | Attribute | Value |
    +-------------+-----------+-----------+-------+
    | a           |         1 | date 1    |    10 |
    | a           |         1 | date 2    |    20 |
    | b           |       0.5 | date 1    |     5 |
    | b           |       0.5 | date 2    |     7 |
    +-------------+-----------+-----------+-------+

1.下一步是添加一个自定义列，它只是将使用量乘以列Value。
1.然后在下一步中删除这两列。
1.最后但同样重要的是，透视属性列，并确保值列设置为自定义列。结果如下：

+-------------+--------+--------+
    | part number | date 1 | date 2 |
    +-------------+--------+--------+
    | a           |     10 |     20 |
    | b           |    2.5 |    3.5 |
    +-------------+--------+--------+

显然，更改列标题等。以满足您的要求。

• 在与材料表连接之前取消透视sales表
• 合并两个表并添加一列（[值]*[用量]）
• 透视date列

表：材料

let
    Source = Excel.CurrentWorkbook(){[Name="materials"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Number", type text}, {"Usage Quantity", type number}})
in
    #"Changed Type"

表：销售额

let
    Source = Excel.CurrentWorkbook(){[Name="sales"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Number", type text}, {"10/16/2023", Int64.Type}, {"10/23/2023", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item Number"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}})
in
    #"Renamed Columns"

表：合并表

let
    Source = Table.NestedJoin(sales, {"Item Number"}, materials, {"Item Number"}, "materials", JoinKind.LeftOuter),
    #"Expanded materials" = Table.ExpandTableColumn(Source, "materials", {"Usage Quantity"}, {"Usage Quantity"}),
    #"Added Custom" = Table.AddColumn(#"Expanded materials", "NewValue", each [Value]*[Usage Quantity]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Date]), "Date", "NewValue", List.Sum)
in
    #"Pivoted Column"

三个PQ公式可以合并，如果你喜欢有更少的查询。

let
    materials = Excel.CurrentWorkbook(){[Name="materials"]}[Content],
    salesSource = Excel.CurrentWorkbook(){[Name="sales"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(salesSource, {"Item Number"}, "Attribute", "Value"),
    sales = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}),
    Source = Table.NestedJoin(sales, {"Item Number"}, materials, {"Item Number"}, "materials", JoinKind.LeftOuter),
    #"Expanded materials" = Table.ExpandTableColumn(Source, "materials", {"Usage Quantity"}, {"Usage Quantity"}),
    #"Added Custom" = Table.AddColumn(#"Expanded materials", "NewValue", each [Value]*[Usage Quantity]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Date]), "Date", "NewValue", List.Sum)
in
    #"Pivoted Column"