☰

MS Excel - 使用 M 代码合并表格

tag-icon • tag-icon microsoft-excel code
MS Excel - 使用 M 代码合并表格

我有一个文件，其中包含 3 个不同的表格，分别位于不同的工作表上。这些表格的列数和行数各不相同，但都有 1 列相同。我之前曾在此链接中询问过我试图实现的目标的简化版本 MS Excel - 合并仅包含部分匹配数据的表格

另一位用户 Ron 给了我一些代码，解决了我想要做的事情的基本前提，所以我拿走了这些代码并尝试编辑它。在第一次尝试中，我尝试将第一张和第二张表中的表格添加到一起。它几乎成功了，但还不够。

前 3 个步骤似乎运行良好，我认为第 4 步（分组行）已起作用，但我不确定。主要问题似乎是步骤“添加自定义”和“添加自定义 1”添加了我需要的前 2 列，但当我添加自定义 2 时，它不是在右侧添加另一列，而是替换了之前添加的列。接下来的几个步骤是我想在剩余的列中添加，它仍然只用新列替换最后一列，因此只有 2 个自定义列。这是我的 3 个表格

Excel 表格

这是我编辑后的代码：

let
    Source1 = Excel.CurrentWorkbook(){[Name=”WACEAchievement”]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name=”MedianATAR”]}[Content],
    combTbl = Table.Combine({Source1,Source2}),

    #”Grouped Rows” = Table.Group(combTbl, {“School”}, {{“Grouped”, each _, type table [School=text, Number of eligible year 12 students=nullable text, percent students who achieved the WACE=nullable text, Number of students with an ATAR=nullable text, percent of students with an ATAR=nullable text, Median ATAR=nullable text]}}),

    #”Added Custom” = Table.AddColumn(#”Grouped Rows”, “Number of eligible year 12 students”, each try
            List.RemoveNulls(Table.Column([Grouped],”Number of eligible year 12 students”)){0}
        otherwise null),

    #”Added Custom1” = Table.AddColumn(#”Added Custom”, “percent students who achieved the WACE”, each try
            List.RemoveNulls(Table.Column([Grouped],”percent students who achieved the WACE”)){0}
        otherwise null),
        
    #”Added Custom2” = Table.AddColumn(#”Added Custom”, “Number of students with an ATAR”, each try
            List.RemoveNulls(Table.Column([Grouped],”Number of students with an ATAR”)){0}
        otherwise null),
        
    #”Added Custom3” = Table.AddColumn(#”Added Custom”, “percent of students with an ATAR”, each try
            List.RemoveNulls(Table.Column([Grouped],”percent of students with an ATAR”)){0}
        otherwise null),
        
    #”Added Custom4” = Table.AddColumn(#”Added Custom”, “Median ATAR”, each try
            List.RemoveNulls(Table.Column([Grouped],”Median ATAR”)){0}
        otherwise null),        
    
    #”Removed Columns” = Table.RemoveColumns(#”Added Custom1”,{“Grouped”})
in
    #”Removed Columns”

以下是不同步骤中发生情况的一些示例。 步骤1 第2步 在此处输入图片描述

这是我遇到的困难的进度截图 重复全部

答案1

如果您不尝试编辑代码而是使用 UI 来获取结果，您会发现这要容易得多。例如，如果我从这里开始：

在此处输入图片描述

我依次在每个表上创建查询，使用“数据”>“从表/范围”。我在 Power Query 编辑器中单击“关闭并加载到”，然后按如下方式配置它：

在此处输入图片描述

即“仅创建连接”

现在我有三个疑问：

在此处输入图片描述

接下来，我使用数据>获取数据>合并查询>合并并按如下方式配置它：

在此处输入图片描述

请注意，在“连接类型”下拉菜单中选择“Full Outer”非常重要。

单击“确定”后，得到以下信息：

在此处输入图片描述

我单击“Table2”列顶部的双箭头并执行以下操作：

在此处输入图片描述

即我取消选择“使用原始列名作为前缀”并保留所有选定的列。

现在我有这个：

在此处输入图片描述

请注意，我现在有两个“学校”列 - “学校”，其中包含表 1 中的学校列表，以及“学校.1”，其中包含表 2 中的学校列表。

在将表 3 中的数据添加到查询之前，我们需要合并这些列。

为了更清楚地说明这一点，我首先通过双击标题并输入新名称来重命名每个学校列：

在此处输入图片描述

接下来，我使用添加列>自定义列并进行如下配置：

在此处输入图片描述

请注意，M 区分大小写，并且 if-then-else 必须是小写。

现在我有了我的新专栏：

在此处输入图片描述

我右键单击 Table1Schools 和 Table2Schools 并选择“删除”，然后将新的“学校”列拖到其余列的左侧（不是绝对必要的，但有助于保持井然有序）。现在我有一个学校列，每行都有一个学校 ID。我还有来自 Table1 和 Table2 的数据：

在此处输入图片描述

接下来，我想将 Table3 与此查询合并。因此，在 Power Query 编辑器中，我在“主页”选项卡上使用“合并查询”。我按如下方式配置它：

在此处输入图片描述

请注意，第一个表实际上是“Merge1”，它是合并 Table1 和 Table2 的最终结果。

按照与上述相同的方式扩展 Table3 列后，得到以下结果：

在此处输入图片描述

因此，我有一行 School 为空。我需要重复我上面遵循的新列过程。因此，我重命名了这些列：

在此处输入图片描述

然后创建一个具有与上述类似公式的新列：

在此处输入图片描述

删除 Merge1School 和 Table3School 后，将新列移动到最左侧，我得到了所需的结果：

在此处输入图片描述

再次强调，我建议尽可能使用 UI，因为它可以节省大量时间。作为参考，结果查询如下：

let
    Source = Table.NestedJoin(Table1, {"School"}, Table2, {"School"}, "Table2", JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"School", "Data 2"}, {"School.1", "Data 2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Table2",{{"School", "Table1Schools"}, {"School.1", "Table2Schools"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "School", each if [Table1Schools] is null then [Table2Schools] else [Table1Schools]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Table1Schools", "Table2Schools"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"School", "Data 1", "Data 2"}),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"School"}, Table3, {"School"}, "Table3", JoinKind.FullOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(#"Merged Queries", "Table3", {"School", "Data 3"}, {"School.1", "Data 3"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Table3",{{"School", "Merge1School"}, {"School.1", "Table3School"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "School", each if [Merge1School] is null then [Table3School] else [Merge1School]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Merge1School", "Table3School"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"School", "Data 1", "Data 2", "Data 3"})
in
    #"Reordered Columns1"

当然，在这个查询中列重命名步骤并不是严格必要的（因为它们在重命名后不久就被删除），您可以等到最后一步再删除您不想要的列。

答案2

您已经非常接近完成代码编辑了：

例如你写道：

 #”Added Custom2” = Table.AddColumn(#”Added Custom”, “Number of students with an ATAR”, each try
            List.RemoveNulls(Table.Column([Grouped],”Number of students with an ATAR”)){0}
        otherwise null),

但请注意，在第一行中，您正在将列添加到#"Added Custom"表中。但在每个步骤中，您都需要将自定义列添加到由前一步骤，以保留前面的表格。

因此#"Added Custom2"应添加到#"Added Custom1"表中；等等。

所以与其：（另请注意，您可以在其中拥有所有三个表格Table.Combine）

let
    Source1 = Excel.CurrentWorkbook(){[Name="WACE"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="ATAR"]}[Content],
    Source3 = Excel.CurrentWorkbook(){[Name="CERTII"]}[Content],
    combTbl = Table.Combine({Source1,Source2,Source3}),

    #"Grouped Rows" = Table.Group(combTbl, {"School"}, {{"Grouped", each _, type table [School=text, Data 1=nullable text, Data 2=nullable text]}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"School", Order.Ascending}}),

    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Number of eligible year 12 students", each try
            List.RemoveNulls(Table.Column([Grouped],"Number of eligible year 12 students")){0}
        otherwise null),

    #"Added Custom1" = Table.AddColumn(#"Added Custom", "percent students who achieved the WACE", each try
            List.RemoveNulls(Table.Column([Grouped],"percent students who achieved the WACE")){0}
        otherwise null),

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Number of students with an ATAR", each try
            List.RemoveNulls(Table.Column([Grouped],"Number of students with an ATAR")){0}
        otherwise null),

    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "percent of students with an ATAR", each try
            List.RemoveNulls(Table.Column([Grouped],"percent of students with an ATAR")){0}
        otherwise null),

    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Median ATAR", each try
            List.RemoveNulls(Table.Column([Grouped],"Median ATAR")){0}
        otherwise null),

    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Number completed Cert II or higher but less than four ATAR courses", each try
            List.RemoveNulls(Table.Column([Grouped],"Number completed Cert II or higher but less than four ATAR Courses")){0}
        otherwise null),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Grouped"})

in
    #"Removed Columns"

在此处输入图片描述

笔记考虑到数据的性质，合并三个表后，您可以通过选择从 UI 中完成其余操作：

  • 通过...分组School
  • 选择Advanced
  • Sum然后使用（或其他算术）运算 为每一列添加一个聚合。
    • 由于每个学校/列组合只有一个条目，各种算术运算（但不计数）将返回适当的值。

聚合示例

在此处输入图片描述

  • 请注意，由于我很懒，所以我按字母顺序命名了新列。
  • 然后，我在高级编辑器中手动添加了几行，以便根据它们的原始名称命名它们。
  • 当然，您可以通过输入New Column Name与要聚合的列相同的名称来消除这种情况。
  • 我不知道这种方法或前一种方法哪种方法更有效。
let
    Source1 = Excel.CurrentWorkbook(){[Name="WACE"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="ATAR"]}[Content],
    Source3 = Excel.CurrentWorkbook(){[Name="CERTII"]}[Content],

combTbl = Table.Combine({Source1,Source2,Source3}),

    #"Grouped Rows" = Table.Group(combTbl, {"School"}, {
        {"a", each List.Sum([Number of eligible year 12 students]), type nullable number}, 
        {"b", each List.Sum([percent students who achieved the WACE]), type nullable number}, 
        {"c", each List.Sum([Number of students with an ATAR]), type nullable number}, 
        {"d", each List.Sum([percent of students with an ATAR]), type nullable number}, 
        {"e", each List.Sum([Median ATAR]), type nullable number}, 
        {"f", each List.Sum([Number completed Cert II or higher but less than four ATAR Courses]), type nullable number}
            }),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"School", Order.Ascending}}),

//rename the aggregated columns
newColNames = List.RemoveFirstN(Table.ColumnNames(combTbl),1),
curColNames = List.RemoveFirstN(Table.ColumnNames(#"Grouped Rows"),1),
renameList = List.Zip({curColNames,newColNames}),
reName = Table.RenameColumns(#"Sorted Rows",renameList)

in
    reName

相关内容