Forum Discussion
Creating a single table from 2 tables
Hi i am trying to create a single table in BI combined from 2 separate data sources.
i only need to use one column, i.e the account number.
Both tables will include data that is duplicated and each table will also have unique values and i want to create a single map table of all account numbers that appear in both of the previous tables to create a master table.
what is the easiest way to do this?
in Power Query (replace the red code with your references)
let Tab1 = Table1, Tab1_single_column = Table.SelectColumns(Tab1,{"Id"}), Tab2 = Table2, Tab2_single_column = Table.SelectColumns(Tab2,{"Id"}), #"Appended Query" = Table.Combine({Tab1_single_column, Tab2_single_column}), #"Removed Duplicates" = Table.Distinct(#"Appended Query") in #"Removed Duplicates"Use the Append Query and then You would arrive at below result
Then you can right click on the account number and click Remove Duplicates. This will help to remove the duplicated Account Value. Finally you will arrive at below.
Hope this helps :smileywink:
2 Replies
- Stachu
Community Champion
in Power Query (replace the red code with your references)
let Tab1 = Table1, Tab1_single_column = Table.SelectColumns(Tab1,{"Id"}), Tab2 = Table2, Tab2_single_column = Table.SelectColumns(Tab2,{"Id"}), #"Appended Query" = Table.Combine({Tab1_single_column, Tab2_single_column}), #"Removed Duplicates" = Table.Distinct(#"Appended Query") in #"Removed Duplicates" 
KasunpathiranaAdvocate IV
Use the Append Query and then You would arrive at below result
Then you can right click on the account number and click Remove Duplicates. This will help to remove the duplicated Account Value. Finally you will arrive at below.
Hope this helps :smileywink: