excel - 如何通过引用列位置使用 Excel PowerQuery 将派生列添加到透视表

问题描述

我正在使用具有透视数据的 Excel 中的 PowerQuery 创建一个常规报告。枢轴以日期顺序将最近的四个季度作为列名称（例如 30/09/2018、31/12/2018、31/03/2019、30/06/2019）。每个日期列中的值都是数字。每次运行报告时，列名都会发生变化，因为……新日期！

这些行代表不同的实体（例如，妈妈银行、爸爸银行、爷爷银行）。我需要创建一些派生列来计算最新季度列（最后一列）和上一季度列（例如 3 个月变动、6 个月变动）之间的差异。以下是一些示例数据：

这是数据在数据透视之前的样子：

Entity_Name       Quarter Date    Value
Bank of Dad       30/09/2018      4
Bank of Dad       31/12/2018      2
Bank of Dad       31/03/2019      3
Bank of Dad       30/06/2019      3
Bank of Grandpa   30/09/2018      4
Bank of Grandpa   31/12/2018      6
Bank of Grandpa   31/03/2019      1
Bank of Grandpa   30/06/2019      2
Bank of Mum       30/09/2018      5
Bank of Mum       31/12/2018      5
Bank of Mum       31/03/2019      4
Bank of Mum       30/06/2019      4

在枢轴之后：

Entity_Name       30/09/2018     31/12/2018     31/03/2019     30/06/2019
Bank of Grandpa   4              6              1              2
Bank of Mum       5              5              4              4
Bank of Dad       4              2              3              3

这是我想要到达的地方（最近一个季度减去三个月前和六个月前）：

Entity_Name       Movement (3 months)   Movement (6 months)
Bank of Grandpa   1                     -4
Bank of Mum       0                     -1
Bank of Dad       0                      1

我在互联网上进行了一次很好的搜索，我发现了类似但不完全存在的示例（通常与重命名列或从列中过滤值有关）。我的问题实际上比我发现的要简单得多，所以我觉得这应该相对简单，我只是遗漏了一些其他人不需要质疑的明显问题。我是第一次在 PowerQuery 工作的 pandas/python 女孩（我需要把这个交给客户定期运行，所以我需要报告来“谈论”他们的语言 - 即 excel。）PowerQuery 确实做了一些事情好吧（在 excel 中加入和分组？ - 很酷！）。其他的东西有点打架——像这样！

我以两种方式看待这个问题 - 首先，尝试在枢轴之前执行此操作，这样我就不会为更改列名而苦苦挣扎。这是一个卷发。如果只有一个实体可能会更简单，但多个实体会使它变得棘手。我的首选方法与之前编写此报告的方式一致（在 excel 中，不使用 power query）是从透视数据中派生列。为此，我需要在添加新列时按位置引用列。

如果我可以解决通过位置引用获取包含另一列值的列返回的问题，那么我已经完成了 99% 的工作，因此我正在寻求帮助来解决问题的特定部分。

即通过使用列的位置引用（而不是明确的列名）来达到这一点：

Entity_Name        30/09/2018    ...    Values_in_col_1   
Bank of Dad        4             ...    4
Bank of Grandpa    4             ...    4
Bank of Mum        5             ...    5

这是我尝试过的：

let
     Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
     #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entity name", type text}, {"Quarter date", type date}, {"Value", Int64.Type}}),
      #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Entity name", Order.Ascending}, {"Quarter date", Order.Ascending}}),
     #"PrevTable" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"Quarter date", type text}}, "en-NZ"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Quarter date", type text}}, "en-NZ")[#"Quarter date"]), "Quarter date", "Value", List.Sum),
     #"Column names" = Table.ColumnNames(#"PrevTable"),
     #"Test 1" = Table.AddColumn(#"PrevTable", "Test1", each #"Column names"{1}),
     #"Test 2" = Table.AddColumn(#"Test 1", "Test2", each Table.SelectRows(#"PrevTable", each(Record.Field(_,Table.ColumnNames(#"PrevTable"){1}))))
in
     #"Test 2"

测试 1：我已经设法创建一个列名列表并通过索引引用它们 - 但返回的是一列值，它们是列名，而不是实际值！哈哈。

测试 2：我还尝试了其他方法来查看是否可以检索行值（尝试修改我发现的也用于过滤的代码），但这会返回一个没有列的表。

这是我从该代码中得到的表格：

Entity_Name        30/09/2018    ...    Test1        Test2   
Bank of Dad        4             ...    30/09/2018   [Table]
Bank of Grandpa    4             ...    30/09/2018   [Table]
Bank of Mum        5             ...    30/09/2018   [Table]

如果我点击“表格”值，我会得到这种类型的响应：

Expression.Error：我们无法将值 4 转换为逻辑类型。

任何帮助将不胜感激 - 学习的工具太多，大脑太少了！

标签： excelpivotpowerquery

excel - 如何通过引用列位置使用 Excel PowerQuery 将派生列添加到透视表

问题描述

解决方案

推荐阅读