Forum Discussion
Helper IMerging Several Columns from 2 Different Tables to 1 Table
In my report, I would like one table that shows the title and status. Data comes from two different tables. When I attempt to use Merge Queries into new and select title in both tables, it brings over ALL of the first tables columns and not merging the title column I selected. Given I can only do one column using merge, is there a better method?
I also want to identify what table the data came from as a new column.
Hi,
Thanks for the solution asadmd93 offered, and i want to offer some more information for user to refer to.
hello deeave , if you want to the same columns in two tables, you can refer to the follwing sample.
Sample data
Table A
Table B
Create a blank query, then input the following code in advanced editor.
let Columnname=List.Intersect({Table.ColumnNames(#"Table A"),Table.ColumnNames(#"Table B")}), Source1=Table.SelectColumns(#"Table A",Columnname), Source2=Table.SelectColumns(#"Table B",Columnname), Combine=Table.Combine({Source1, Source2}) in CombineOutput
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi,
Thanks for the solution asadmd93 offered, and i want to offer some more information for user to refer to.
hello deeave , if you want to the same columns in two tables, you can refer to the follwing sample.
Sample data
Table A
Table B
Create a blank query, then input the following code in advanced editor.
let Columnname=List.Intersect({Table.ColumnNames(#"Table A"),Table.ColumnNames(#"Table B")}), Source1=Table.SelectColumns(#"Table A",Columnname), Source2=Table.SelectColumns(#"Table B",Columnname), Combine=Table.Combine({Source1, Source2}) in CombineOutput
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
deeaveThis worked perfectly to combine the tables, thank you! I updated the code to create a table to identify the source table so I could display in my report. Code below:
let
// Select the columns from Project_ideas and add SourceTable column
Source1 = Table.AddColumn(
Table.SelectColumns(Project_ideas, {"Service", "Status", "Title"}),
"SourceTable",
each "Project_ideas"
),// Select the columns from Project_Discovery and add SourceTable column
Source2 = Table.AddColumn(
Table.SelectColumns(Project_Discovery, {"Service", "Status", "Title"}),
"SourceTable",
each "Project_Discovery"
),// Combine the two tables
CombinedTable = Table.Combine({Source1, Source2})
in
CombinedTable
asadmd93Advocate I
Use Append queries instead of merge queries. Here is how you can do it:
Load both tables
Append Queries:
- In Power Query Editor, go to the Home tab and select Append Queries.
- Choose Append Queries as New to create a new table.
- Select the two tables you want to append.
Add a Custom Column to Identify the Source Table:
- Before appending, add a custom column to each table to identify the source.
- For Table1, add a custom column named “Source” with the value “Table1”.
- For Table2, add a custom column named “Source” with the value “Table2”.
Select Only the Required Columns:
- After appending, select only the columns you need (Title, Status, and Source)
If my post helps, then please consider Accept it as the solution to help the other members find it more quickly.