Combining columns from different Tables but in same orders

[Anonymous]

Dear Community Members, 

Request your help with below query -

I have 14 tables with similar data and 5 columns per table

Example of two tables below:

Table 1

Cif ID	Name
1711	None
6947	Somoene
1276	Noone
5645	Anyone

Table 2

CRN	Name
2868	Yum
6502	dum
6498	some
5362	ton

I want to combine all the columns in order with each representing the subsequent value in a new table like below:

Cif ID	Name
1711	None
6947	Somoene
1276	Noone
5645	Anyone
2868	Yum
6502	dum
6498	some
5362	ton

PS - Please note that columns are not in exact order header wise hence cannot use Append query , also tried below function 

= Table.Combine({
Table.RenameColumns(
Table.SelectColumns(#"Table 1","CIF Id") ,{{"CIF Id","CIF ID"}})
,
Table.RenameColumns(
Table.SelectColumns(#"Table 2","CRN") ,{{"CRN","CIF ID"}})
,
Table.RenameColumns(
Table.SelectColumns(#"Table 1","Name") ,{{"Name","Name"}})
,
Table.RenameColumns(
Table.SelectColumns(#"Table 2","Name") ,{{"Name","Name"}})

})

But it give below result :

Cif ID	Name
1711	null
6947	null
1276	null
5645	null
2868	null
6502	null
6498	null
5362	null
null	None
null	Somoene
null	Noone
null	Anyone
null	Yum
null	dum
null	some
null	ton

Please help

[AlexisOlson · Accepted solution]

This pattern should work:

let
    Source =
    Table.SelectColumns(
        Table.Combine(
            {
                Table.RenameColumns(Table1, {{"ID1", "ID_Output"}, {"Name1", "Name_Output"}}),
                Table.RenameColumns(Table2, {{"ID2", "ID_Output"}, {"Name2", "Name_Output"}}),
                Table.RenameColumns(Table3, {{"ID3", "ID_Output"}, {"Name3", "Name_Output"}})
            }
        ),
        {"ID_Output", "Name_Output"}
    )
in
    Source

Update the column names as appropriate for your situation.

[ImkeF]

Hi @Anonymous ,
sorry, but I don't understand the requirement.

[ImkeF]

Oh yes, sorry - my fault:

= Table.Combine({
Table.RenameColumns(
Table.SelectColumns(#"Table 1","CIF Id", "Name") ,{{"CIF Id","CIF ID"}})
,
Table.RenameColumns(
Table.SelectColumns(#"Table 2","CRN", "Name") ,{{"CRN","CIF ID"}})
})

[Anonymous]

I am troubling you..

so there are 14 tables, all dont have the header as "Name", so ultimately i have to convert the "Name" to "Name"

[ImkeF]

Hi @Anonymous ,
please try the following instead:

= Table.Combine({
Table.RenameColumns(
Table.SelectColumns(#"Table 1","CIF Id", "Name") ,{{"CIF Id","CIF ID", "Name"}})
,
Table.RenameColumns(
Table.SelectColumns(#"Table 2","CRN", "Name") ,{{"CRN","CIF ID", "Name"}})
})

[Anonymous]

Hi,

It give below error:

We cannot convert the value "Name" to type Number.