我正在寻找一些电源查询帮助。一年来,我为40K产品提供了大量的销售数据。对于每一天的每种产品,我都需要添加28天的销售专栏。
我本质上想做类似下面的sumifs,但在m。
中= sumifs([sales],[产品代码],[此产品代码],[date],< = [date],[date],[date],> = [date date] -28))
然后尝试一下,它应该起作用,但可能会在爬网
上这样做 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales", Int64.Type}, {"Product Code", type text}, {"Date", type date}}),
TotalAmountAdded = Table.AddColumn(Source, "Total Amount", (i) => List.Sum(Table.SelectRows(Source, each ([Product Code] = i[Product Code] and [Date]<=i[Date] and [Date]>=Date.AddDays(i[Date],-28)))[Sales]), type number )
in TotalAmountAdded
添加带有日期逻辑的自定义列(基于您的示例sumif公式),过滤新列以获取相关行,然后按产品代码和总和销售进行分组。假设源数据在表1中,带有三列(销售,产品代码,日期),代码将为
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales", Int64.Type}, {"Product Code", type text}, {"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "AddMe", each if [Date]<=DateTime.Date(DateTime.LocalNow()) and [Date]>=Date.AddDays(DateTime.Date(DateTime.LocalNow()),-28) then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([AddMe] = 1)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Product Code"}, {{"ProductSales", each List.Sum([Sales]), type number}})
in #"Grouped Rows"