Forum Discussion
Compare columns from two tables side by side
Hi,
I have two tables, which should contain the same values. I try to compare two columns to display missing or different values.
Table 1:
| TableNameSQL | ColumnNameSQL |
| Companies | Name_Id |
| Companies | Name |
| Address | Country |
| Address | City |
| Address | Street |
| Address | Company_Id |
| Sales | Company_Id |
| Sales | Product |
| Sales | Value |
Table 2:
| TableNameLocal | ColumnNameLocal |
| Companies | Name_Id |
| Companies | Name |
| Address | Country |
| Address | City |
| Address | Street |
| Sales | CompanyId |
| Sales | Product |
| Sales | Value |
The result I try to achieve is a table like the below one:
| TableNameLocal | ColumnNameSQL | ColumnNameLocal |
| Companies | Name_Id | Name_Id |
| Companies | Name | Name |
| Address | Country | Country |
| Address | City | City |
| Address | Street | Street |
| Address | Company_Id | |
| Sales | Company_Id | |
| Sales | CompanyId | |
| Sales | Product | Product |
| Sales | Value | Value |
I tried various things, including merging these two tables, but I keep getting the below result (multiple values from the second table):
Can you please help me if it is possible to achieve what I am trying to do?
2 Replies
- amitchandak
Super User
MoOnan , Merge the two tables by selecting multiple tables using shift/ctrl
Or create a combine key(column) in both tables, then create a common key table with that key and both columns, join the new table with both tables, and analyze with a column of the common table
Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solution-8e3eccb41bdaPower BI: Power Query Functions one must know
: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=36407s
Power BI - Cheat Sheet: Power Query Functions one must know
https://amitchandak.medium.com/cheat-sheet-power-query-functions-one-must-know-a8531b6e321aHi amitchandak
Thank you for the tips, your video about common tables was very informative!
As I mentioned earlier merging wasn't working for me. Also I am not sure what you mean by this: "Or create a combine key(column) in both tables, then create a common key table with that key and both columns, join the new table with both tables, and analyze with a column of the common table" - I'm sorry, I'm quite a beginner 😞 But I followed the instructions from your video and indeed I was able to get a nice table with comparing columns. However, I still have two issues I wanted to ask you about, can you please help me?
1. First issue: For some reason one value was missing in the result table: CompanyId in ColumnNameLocal.2. Second issue: I had to modify my dataset a little and add two reference tables with TableNames columns. Although I repeated steps from the previous attempt, this time I received this error:
These are my tables:ObjectId ColumnNameSQL 1 Name_Id 1 Name 2 Country 2 City 2 Street 2 Company_Id 3 Company_Id 3 Product 3 Value 4 Name ObjectId TableNameSQL 1 Companies 1 Companies 2 Address 2 Address 2 Address 3 Sales 3 Sales 3 Sales 3 Sales 4 Customers FileId ColumnNameLocal 205 Name_Id 205 Name 301 Country 301 City 301 Street 104 CompanyId 104 Product 104 Value FileId TableNameLocal 205 Companies 301 Address 104 Sales
And these are the relations:
