Forum Discussion
Combining columns from different Tables but in same orders
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
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 SourceUpdate the column names as appropriate for your situation.
6 Replies
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"}}) })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 SourceUpdate the column names as appropriate for your situation.
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"}}) })Hi Anonymous ,
sorry, but I don't understand the requirement.
