【Power Query】【M语言】【Excel】【Power BI】在展开包含Table表格的列前，对每个Table表格进行逆透视：

用于在Power BI或Excel中处理数据。它的作用是在Power Query展开Content列前，对数据进行逆透视操作。

假设[Data]列中有以下数据，他们都是最常见的二维表：

单位	部门	2023-01	2023-02	2023-03
A	aa	100	200	300
B	bb	150	250	350

单位	部门	2024-01	2024-02	2024-03
A	aa	100	200	300
B	bb	150	250	350

适用场景：

假如后面又有25年26年的数据，那你展开之后有多少列了，如果你每列都需要（意思是你展开所有列，没做筛选），那还好说，数据完整。如果你已经这次展开已经做了列筛选（比如你筛选了“部门”、“2023-01”、“2023-02”、“2023-03”、“2024-01”、“2024-02”、“2024-03”）下次你增加了25年数据，你刷新就刷不出来25年数据，数据不完整，你还得改M语言代码。

所以你就需要在展开列前，对表格进行逆透视。下面是代码：

代码结构：

已添加自定义 = Table.AddColumn(筛选的行, "自定义", each Table.UnpivotOtherColumns(
        [Data],
        {"单位", "部门"},
        "报表周期","值"
    )),

介绍一下里面的几个函数：

1. Table.AddColumn

• 作用: 在现有的表中添加一个新列。

• 参数:

  • 筛选的行: 这是当前的表或查询结果，表示已经筛选过的数据。（上一个步骤的结果）

  • "自定义": 这是新列的名称。

  • each ...: 这是一个匿名函数，用于定义新列的值。

2. Table.UnpivotOtherColumns

• 作用: 对表中的列进行逆透视操作，将多列转换为键值对的形式。

• 参数:

  • [Data]: 这是当前行中的某个列（通常是一个嵌套的表），需要进行逆透视操作。

  • {"单位", "部门"}: 这是不需要逆透视的列名列表。这些列将保持不变。

  • "报表周期": 这是新生成的列名，用于存储原始列名（即被逆透视的列名，比如“2023-01”、“2023-02”、“2023-03”、“2024-01”、“2024-02”、“2024-03”）。

  • "值": 这是新生成的列名，用于存储原始列中的值（就是上表中的100/150/200等等）。

3. each

• 作用: 这是一个匿名函数，用于对每一行进行操作。在这里，它表示对每一行中的[Data]列进行逆透视操作。

代码的整体作用

1. 添加新列: 在筛选的行表中添加一个名为"自定义"的新列。

2. 逆透视操作: 对每一行中的[Data]列进行逆透视操作，保留"单位",和"部门"列，将其他列转换为键值对的形式，并存储在"报表周期"和"值"列中。

代码的结果

逆透视后，表格是这样的。

单位	部门	报表周期	值
A	aa	2023-01	100
A	aa	2023-02	200
A	aa	2023-03	300
B	bb	2023-01	150
B	bb	2023-02	250
B	bb	2023-03	350

单位	部门	报表周期	值
A	aa	2024-01	100
A	aa	2024-02	200
A	aa	2024-03	300
B	bb	2024-01	150
B	bb	2024-02	250
B	bb	2024-03	350

然后你再把这个“自定义”列展开，就只有“单位”、“部门”、“报表周期”、“值”四列啦。

下面是实操：

1、同时复习知识点，使用 Excel.CurrentWorkbook() 函数来引用当前正在使用的 Excel 文件。

2、如果在展开前不做逆透视：很多列

3、插入逆透视的代码：增加一个自定义列（有一些变量根据你的情况做改变）

4、最后展开自定义列，我们看到要展开的列就没那么多了，就得到结果啦。