Merging Several Columns from 2 Different Tables to 1 Table

[deeave]

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.  

[v-xinruzhu-msft · Accepted solution]

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
    Combine

Output

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.

[deeave]

This 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

[asadmd93]

Use Append queries instead of merge queries. Here is how you can do it:

1. Load both tables

2. 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.

3. 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”.

4. 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.