我有一个文件，每张纸上有 3 个不同的表格。这些表都有不同数量的列和行，但它们都有 1 列共同。我之前在此链接中询问了我正在尝试完成的简化版本 MS Excel - 仅将表格与一些匹配数据合并
另一个用户，罗恩给了我一些代码，解决了我想做的基本前提，所以我拿了那个代码，我试着编辑它。在我的第一次尝试中，我尝试将第一张和第二张表中的表格添加在一起。它几乎奏效了，但还没有完全奏效。
前 3 步似乎工作得很好，我认为第 4 步（分组行）已经奏效，但我不确定。主要问题似乎是添加自定义和添加自定义 1 的步骤放在了我需要的前 2 列中，但是当我到达添加自定义 2 时，而不是在右侧添加另一列，它替换了之前添加的列。我想在其余列中添加的接下来的几个步骤，它仍然只用新列替换最后一列，因此只有 2 个自定义列。这是我的 3 张桌子
这是我编辑后的代码：
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”
以下是不同步骤所发生情况的一些示例。
这是我卡住的进度镜头
如果您不尝试编辑代码而是使用 UI 来获取结果，您会发现这更容易。例如，如果我从这里开始：
我依次使用 Data>From Table/Range 在每个表上创建一个查询。我在 Power Query 编辑器中单击“关闭并加载到”并将其配置如下：
即“仅创建连接”
现在我有三个查询：
接下来，我使用 Data>Get Data>Combine Queries>Merge 并像这样配置它：
请注意，在 Join Kind 下拉列表中选择“Full Outer”非常重要。
单击确定后，我有这个：
我单击“Table2”列顶部的双箭头并执行以下操作：
即我取消选择“使用原始列名作为前缀”并保留所有列选中。
现在我有这个：
请注意，我现在有两个“学校”列——“学校”包含 Table1 中的学校列表，“School.1”包含 Table2 中的学校列表。
We need to merge these columns before adding the data from Table3 to the query.
To help make this clearer, I first rename each of those school columns by double-clicking the header and typing a new name:
Next, I use Add Column>Custom Column and configure it like this:
Note that M is case-sensitive and the if-then-else must be in lower case.
Now I have my new column:
I right click Table1Schools and Table2Schools and select "Remove", then drag the new "School" column to the left of the remainder of the columns (not strictly necessary, but helps to stay organized). Now I have a School column with a school ID in each row. I also have the data from Table1 and Table2:
Next, I want to Merge Table3 with this query. So, in the Power Query Editor, I use Merge Queries on the Home tab. I configure it like this:
Note that the first table is actually "Merge1", which was the end-result of merging Table1 and Table2.
After expanding the Table3 column in the same way as above, I have this:
So, I have one row where School is null. I need to repeat the new column process I followed above. So, I rename the columns:
Then create a new column with a formula similar to above:
After removing Merge1School and Table3School, then moving the new column to the far-left, I have the required result:
Again, I recommend using the UI where possible as it will save you lots of time. For info, the resulting query is this:
Of course, the column renaming steps are not strictly necessary in this query (since they are removed shortly after renaming them) and you could wait until the last step to remove columns you don't want.
You were very close in doing your code editing:
You wrote, for example:
But note that in the first line, you are adding the column to the
#"Added Custom"table. But at each step, you need to add the custom column to the Table generated by the preceding step, in order to preserve the preceding table.So
#"Added Custom2"should be added to the#"Added Custom1"table; and so forth.So, instead: (and also note you can have all three tables in your
Table.Combine)Note Given the nature of your data, after you combine the three tables, you could do the rest from the UI by selecting:
SchoolAdvancedSum(or other arithmetic) operation.聚合示例
New Column Name要聚合的列相同的名称来消除这种情况。