excel - 电源查询；计算缺少按数字顺序排列的某个值并创建附加行

Question

我每周都会生成一份报告，确定我公司本周和未来几周对零件的需求。我生成的报告从客户文件中加载数据，我使用 Power Query 下载、转换并加载到我的报告中。该报告显示了该年的周数和该周的需求。我面临的问题是客户报告跳过一周（第 26 周）并直接进入第 27 周（查看附图）。

我可以在 Power Query 中采取哪些步骤来计算缺少周数的时间，并为该周数创建一个额外的行，并用“0”填充“所需数量”字段，同时保持“当前余额”字段与前一周相同。

我附上了从 PQ 生成的表格和编辑器中的 PQ 表格的图像。

标签： exceltransformpowerqueryexcel-2016

Answer 1

这可能会有所帮助。我从与您的屏幕截图类似的表格开始：

然后我将它作为 Table1 带入 Power Query：

然后我添加了一个索引列，从 1 开始：

然后我添加了一个自定义列来确定是否跳过了一周。它为此使用 Index 列和 WeekNumber 列。（try 否则会处理到达最后一条记录时发生的错误。）：

然后我过滤了自定义列，只显示“否”的行：

然后，为了添加丢失的周记录，我单击了功能按钮... ...在功能栏区域键入并按下回车键。= Table.TransformColumns(Custom1, {{"Qty Required", each 0},{"Requirement Date", each 0}, {"WeekNumber", each _ + 1}})我还将应用步骤的名称从 Custom1 更改为 Transform Table（在 Power Query 窗口的右侧）：

然后我点击 Home > Append Queries 并在下拉列表中选择 Table1，然后点击 OK。

然后我在功能栏区域将#"Transform Table" 的第二个实例更改为#"Added Custom" 并按下回车键。这会将先前缺失的几周的新记录附加到先前已经存在的记录中，这些记录存在于 #"Added Custom" 应用步骤（基本上也是以前的表状态）中，以形成完整的集合：

然后我按 WeekNumber 排序：

然后我删除了索引和自定义列：

这是我的 M 代码：

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Part Number", Int64.Type}, {"Part Description", type text}, {"Current Balance On Hand", Int64.Type}, {"Qty Required", Int64.Type}, {"Requirement Date", type datetime}, {"WeekNumber", Int64.Type}, {"Year", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try if  #"Added Index"{[Index]-1}[WeekNumber]+1 = #"Added Index"{[Index]}[WeekNumber] then "Yes" else "No" otherwise "Yes"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "No")),
#"Transform Table" = Table.TransformColumns(#"Filtered Rows", {{"Qty Required", each 0},{"Requirement Date", each 0}, {"WeekNumber", each _ + 1}}),
#"Appended Query" = Table.Combine({#"Transform Table", #"Added Custom"}),
#"Sorted Rows" = Table.Sort(#"Appended Query",{{"WeekNumber", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Custom"})
in
#"Removed Columns"