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

Question

背景

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

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

问题

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

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

我尝试了 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

示例输出数据

示例输出

Answer 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 等）。