如何在权力查询中获得给定累计余额的第一条记录的期初余额

Question

给定下表，其中开始日期是可变的，因为它可以从一个月的任何日期开始：

• 组件|类型|日期|累计余额
• A|PO | 240年1月31日
• A|PO | 2月1日| 240
• B|PO | 300年1月28日
• B|PO | 300年1月29日
• A|SO|100年1月31日
• A|SO|100年2月1日

我需要计算第一个期初余额，只给定累积余额，它由组件+类型重置

• 组件|类型|日期|期初余额
• A|PO | 240年1月31日
• A|PO |0年2月1日
• B|PO | 300年1月28日
• B|PO |0年1月29日
• A|SO|100年1月31日
• A|SO|2月1日|0

如有任何帮助或建议，我们将不胜感激！

谢谢Andrea

Answer 1

您可以使用粘贴到home中的一些自定义PowerQuery代码。。。高级编辑器。。。

假设数据加载到Table1中，第三行将添加一列，该列是具有匹配Component和Type的所有行的最短日期。第四行检查当前行的日期是否与最小值匹配；如果是，则显示余额，否则为零

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Component", type text}, {"Type", type text}, {"Date", type date}, {"AccumulateBalance", Int64.Type}}),
AddMinDateColumn = Table.AddColumn(#"Changed Type", "Earliest Date", (thisrow) => List.Min(Table.SelectRows(#"Changed Type", each [Component] = thisrow[Component] and [Type] = thisrow[Type])[Date]), type date),
#"Added Custom" = Table.AddColumn(AddMinDateColumn, "OpenBalance", each if [Date]=[Earliest Date] then [AccumulateBalance] else 0)
in #"Added Custom"

另一种方法是选择Component和Type列，并使用Date列和AllRows中的最小值对它们进行分组。若展开"累计余额"列并删除重复项，则会得到一个包含最小日期及其值的表。然后，您可以将其合并回与"组件"、"类型"one_answers"日期"匹配的原始表中，并展开"余额"字段。下面的示例可以粘贴到主页。。。高级编辑器。。。假设数据已加载到表1 中

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Component", type text}, {"Type", type text}, {"Date", type date}, {"AccumulateBalance", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Component", "Type"}, {{"MinDate", each List.Min([Date]), type date}, {"Data", each _, type table}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"AccumulateBalance"}, {"Data.AccumulateBalance"}),
Table2= Table.Distinct(#"Expanded Data"),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Component", "Type", "Date"},Table2,{"Component", "Type", "MinDate"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Data.AccumulateBalance"}, {"OpeningBalance"})
in #"Expanded Table2"