Power Query. Merged 2 tables, but after renaming column name from 2nd table, expand column does not select this renamed column

[martinli]

Hi,

I have two tables, Table 1 and Table 2.

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]INDEX[/TD]
[TD="width: 64"]Sex[/TD]
[TD="width: 64"]Age[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]12[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]INDEX[/TD]
[TD="width: 64"]Name
[/TD]
[TD="width: 64"]School[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]Tom[/TD]
[TD="align: center"]Test[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Mary[/TD]
[TD="align: center"]Test1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Tommy[/TD]
[TD="align: center"]Test[/TD]
[/TR]
</tbody>[/TABLE]


I merged the two tables into Marge1

[TABLE="width: 274"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]INDEX[/TD]
[TD="align: center"]Sex[/TD]
[TD="align: center"]Age[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]School[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]Tom[/TD]
[TD="align: center"]Test[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]Mary[/TD]
[TD="align: center"]Test1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]Tommy[/TD]
[TD="align: center"]Test[/TD]
[/TR]
</tbody>[/TABLE]


If i rename Table 2 column heading from School to test, the Merge does not display this renamed column. Is there an easy solution for this?

Thanks in advance.

 

[martinli]

After trying a couple of things i came up with the following:


let
Source = Table.NestedJoin(Table1,{"INDEX"},Table2,{"INDEX"},"NewColumn",JoinKind.FullOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", List.Difference(Table.ColumnNames(Table2), {"INDEX"}))
in
#"Expanded NewColumn"



I basically tell the table not to select the "INDEX" from the second table. Kind of a hassle but it works.