☰

使用 Power Query 将列表数据转换为基于值的格式化矩阵

tag-icon • tag-icon microsoft-excel power-query
使用 Power Query 将列表数据转换为基于值的格式化矩阵

背景

我最近在一个果园里工作。种植图和其他数据存储在许多不同的文件中，每个文件都有不同的格式和结构，由不同的人维护和创建。当果园发生改变（例如种植一棵树或死亡）时，必须在几个不同的文件上更新信息——这很麻烦，而且极易出错。

我的目标是将所有种植数据存储在一个文件中（只有我才能对其进行编辑），然后可以从中自动生成并分发其他摘要文件和报告。这个问题涉及果园中许多人使用的一种摘要文件样式。它是一个矩阵，与我们的果园布局具有一定的空间相关性（例如，工作表的行和列对应于基本方向并模仿果园的布局）

问题

果园的几位主管更喜欢以矩阵形式查看种植数据，其中列和行的方向与基本方向（北、东、南、西）和果园的布局相关（参见示例）。

我怎样才能以列表形式获取这些数据，并将其转换为具有特定格式的矩阵？

我尝试了 Group By、Transpose、Unpivot Columns、的不同组合Split Column，Pivot Column但均未成功。

要求

我希望有一个“主”工作表可供我维护并进行编辑。其他几个工作表或工作簿将根据我的主工作表自动更新。

  1. 的每个值BlockN都会有一个专门的工作表（注意：在此示例中，只有一个值BlockN，而在实际数据集中目前有 13 个不同的值）。
  2. 每个不同的值RowN在输出中都会有一个专用的列。
    • 中不同值的顺序RowN将被反转（即从 Z --> A 开始按左 --> 右排序）。
    • 每列的标题将是RowN和的唯一值的组合RowNm。
  3. 列中每个不同的值Loc都将有一个专用的行。
  4. 每个输出单元格中的值是Variety和的组合Planting Year。
  5. 根据 的相应值，每个单元格都会有条件格式Status。
    • 生活 = 绿色填充
    • 死区 = 红色填充
    • 未种植=蓝色填充

优先考虑，但并非必需。

  1. 输出中的格式
    • 输出边界周围的基本方向（北、东、南、西）。
    • Block由和 的不同值连接而成的标题BlockNm。

潜在问题？

  1. 这将如何处理空白？有些单元格将是空白的，因为那里没有种植任何东西
  2. 如果有不同的值范围怎么办loc？例如RowN A可能是“001 – 060”，也RowN B可能是“001 – 120”

示例源数据

种类 堵塞 区块数 第N行 行数 洛 栽种年份 地位 行距 位置信息
绿色的 1 果树 A 矮树 001 2022 活的 12 3
红色的 1 果树 A 矮树 002 2020 活的 12 3
可口 1 果树 A 矮树 003 2022 死的 12 3
蓝色的 1 果树 A 矮树 004 2019 未种植 12 3
酸的 1 果树 A 矮树 005 2020 活的 12 3
日出 1 果树 乙 葡萄藤 001 2022 活的 12 3
日出 1 果树 乙 葡萄藤 002 2019 死的 12 3
日出 1 果树 乙 葡萄藤 003 2021 活的 12 3
美丽 1 果树 乙 葡萄藤 004 2020 死的 12 3
美丽 1 果树 乙 葡萄藤 005 2020 活的 12 3
绿色.5 1 果树 C 高大的树木 001 2019 活的 12 3
绿色.4 1 果树 C 高大的树木 002 2019 未种植 12 3
绿色.3 1 果树 C 高大的树木 003 2022 未种植 12 3
绿色.2 1 果树 C 高大的树木 004 2021 活的 12 3
绿色.1 1 果树 C 高大的树木 005 2022 未种植 12 3
大叶子 1 果树 德 灌木 001 2019 未种植 12 3
大叶子 1 果树 德 灌木 002 2019 未种植 12 3
黄色喇叭花 1 果树 德 灌木 003 2022 死的 12 3
橙色喇叭 1 果树 德 灌木 004 2021 活的 12 3
蓝铃花 1 果树 德 灌木 005 2020 未种植 12 3

示例输出数据

示例输出

答案1

以下是一种方法：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Variety", type text}, {"Block", Int64.Type}, {"BlockNm", type text}, {"RowN", type text}, {"RowNm", type text}, {"Loc", Int64.Type}, {"Planting Year", type text}, {"Status", type text}, {"RowSp", Int64.Type}, {"LocSp", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "dataName", each [Variety] &" - "&[Planting Year]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "colName", each [RowN]&": "&[RowNm]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Variety", "Block", "BlockNm", "RowN", "RowNm", "Planting Year", "Status", "RowSp", "LocSp"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[colName]), "colName", "dataName"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Loc", "D: Shrubs", "C: Tall Trees", "B: Vines", "A: Short Trees"})
in
    #"Reordered Columns"

所有这些都可以通过功能区命令完成；以下是输出： 在此处输入图片描述

步骤如下：

  • 来源：原表
  • 更改类型：自动更改类型，但我将日期更改为文本
  • 添加自定义：加入品种和年份列
  • 添加自定义1：连接列 rowN 和 rowNm
  • 删除的列：删除除（Loc 和 2 个创建的自定义列）之外的所有列
  • 透视列：以品种/年份作为数据制作 rowN/rowNm 列（提前不聚合）
  • 重新排列列以进入您的列顺序

在 Excel 中将其转换为最终格式可能会有问题。我不想让您走另一条（可能无利可图的）道路，但加上您的其他要求，您可能需要考虑某种类型的 BI 分析工具（power BI、Tableau 等）。

相关内容