使用PQ将余额表转变为含层次关系的标准表

表格的不同功能

因为制作满足逻辑关系的虚拟数据，时间成本较大。所以，仅填了几行随机的虚拟数据作个模拟。
我们看到的余额表或资产负债表经常是这个样子的 (表 1)： 有时我们希望的表可能是下面这个样子的 (表 2)：
在表1 中，不同级别的科目被排列在了同一列里。比如， 货币资金-支付宝-支付宝3805734 这种多级科目是坚向排布的，它的数据重复体现了 3 次。它的好处时，可以直接找到子级科目汇总好的父级科目的金额。
表2 中只有末级科目， 货币资金-支付宝-支付宝3805734 也只在一行中有体现。
这就造成，如果我们拖拽选中一块区域，将其中的数字相加的话：在表 1 里加出来的数字可能没有意义，里面包含重复的数据；而在表 2 里就没有这个问题。对于表2，我们是可以直接进行透视，看一级科目余额，或者看二级科目余额的。

制作自动将表 1 转表 2 的工具

先使用 PQ 将数据读入

构建只含末级科目的表格

选出其中的末级科目。末级科目的定义是：没有其他科目以它为父级科目。我们只要将科目编码去掉末 2 位就能得到它的父级科目编码。所以，判断某科目是否为末级，只要将所有科目去掉后 2 位，看有没有剩下的编码跟该科目的编码相同即可。可以先使用代码 父级编码 = List.Buffer(List.Transform(源[科目编码],(x)=>Text.Start(x,Text.Length(x)-2))) ，构造一个父级科目的列表 (list)。然后使用 Table. AddColumn (源,"是否末级", each not List. Contains (父级编码,[科目编码])) 为原表添加一列，以科目编码是否在父级编码这个列表中为标准，判断是否是末级科目。筛选出末级科目。完整的 M 函数如下：

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    父级编码 = List.Buffer(List.Transform(源[科目编码],(x)=>Text.Start(x,Text.Length(x)-2))),
    添加是否末级 = Table.AddColumn(源,"是否末级",each not List.Contains(父级编码,[科目编码])),
    筛选的行 = Table.SelectRows(添加是否末级, each ([是否末级] = true))
in
    筛选的行

为所有科目指一级科目、二级科目等

通过 Text.Start([科目编码],4) Text.Start([科目编码],6) 为原表中的科目指定出对应的一级科目与二级科目。如果有更多级，依次再添加 Text.Start([科目编码],8) 等。如库存现金这类只有一级的科目，我们将其二级、三级科目视为其本身，Text.Start 取前 6 位、前 8 位字符时，因为一级科目只有 4 位，所以只能取到 4 位，正好满足下级科目视为其本身这一要求。完整的建立表格的 M 函数如下：

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    更改的类型 = Table.TransformColumnTypes(源,{{"科目编码", type text}}),
    已添加自定义 = Table.AddColumn(更改的类型, "一级", each Text.Start([科目编码],4)),
    已添加自定义1 = Table.AddColumn(已添加自定义, "二级", each Text.Start([科目编码],6)),
    删除的其他列 = Table.SelectColumns(已添加自定义1,{"科目编码", "科目名称", "一级", "二级"})
in
    删除的其他列

合并两表，为所有末级科目指定一级科目编码、二级科目编

至此，我们有了如下两张表：

通过合并表格，将科目层级表中的一级、二级两列附加到末级科目表中。

进行如上操作后，末级科目表会自动生成如下代码。

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    父级编码 = List.Buffer(List.Transform(源[科目编码],(x)=>Text.Start(x,Text.Length(x)-2))),
    添加是否末级 = Table.AddColumn(源,"是否末级",each not List.Contains(父级编码,[科目编码])),
    筛选的行 = Table.SelectRows(添加是否末级, each ([是否末级] = true)),
    合并的查询 = Table.NestedJoin(筛选的行, {"科目编码"}, 科目层级表, {"科目编码"}, "科目层级表", JoinKind.LeftOuter),
    #"展开的“科目层级表”" = Table.ExpandTableColumn(合并的查询, "科目层级表", {"一级", "二级"}, {"一级", "二级"})
in
    #"展开的“科目层级表”"

再次合并，将编码换为科目名称

至此，我们就完成了表格的转变了。下次只要贴上新的数据，点一下刷新就 OK 了。

示例文件下载：

链接：百度网盘
提取码：1234