Forum Discussion
Combining two tables with same ID but different information
Hi, I have two tables which have have the same ID on them however one table combines more informationt than the other, I am looking to combine both into a new table but only show information if the IDs both match and if there are IDs that don't match they don't show on the table.
For example I have these two tables:
| ID | NAME | AMOUNT |
| 1 | Chris | 100 |
| 2 | John | 50 |
| 3 | Stephanie | 23 |
| 4 | John | 25 |
| 5 | Mark | 7 |
| 6 | Chris | 35 |
| MONTH | ID | COLOUR | DAY |
| January | 1 | Blue | Monday |
| March | 2 | Red | Friday |
| April | 3 | Green | Sunday |
| December | 4 | Blue | Tuesday |
| September | 5 | Purple | Monday |
| April | 6 | Orange | Wednesday |
| January | 7 | Red | Saturday |
| May | 8 | Pink | Sunday |
| November | 9 | Green | Tuesday |
| February | 10 | Purple | Monday |
| March | 11 | Pink | Thursday |
| December | 12 | Blue | Friday |
| May | 13 | Red | Sunday |
So I am looking to combine all this information so that it would look something like this:
| ID | NAME | AMOUNT | MONTH | COLOUR | DAY |
| 1 | Chris | 100 | January | Blue | Monday |
| 2 | John | 50 | March | Red | Friday |
| 3 | Stephanie | 23 | April | Green | Sunday |
| 4 | John | 25 | December | Blue | Tuesday |
| 5 | Mark | 7 | September | Purple | Monday |
| 6 | Chris | 35 | April | Orange | Wednesday |
The data I am working with is live from a Sharepoint site so looking for this to update automatically as that site updates.
Thanks in advance for any help anyone can provide on this!
Use Merge as new from Merge Queries in Power query editor,
1.
2. Do join kind inner,
3. You will get your output,
Thanks,
Arul
Hi DylanBanINT ,
please make sure you selected the right fields (ID) and that they have the same data type. It must work if they have the same type and matching values.
If possible, could you share a screenshot of your data tables?
7 Replies
I'm not used to Live connection, but can't you simply Merge the 2 queries in Transform Data? Just selected the Join Kind Inner.
If this is not an option in Live connection, please let me know.
Edit: If you want, you can also create a result table using NATURALINNERJOIN() function.
Result Table = NATURALINNERJOIN(Table1, Table2)Hi, so it seems with the specific information I'm using, that although I can go in and see that there are 100% matches from each column, when I try to merge it shows as no matches
Hi DylanBanINT ,
please make sure you selected the right fields (ID) and that they have the same data type. It must work if they have the same type and matching values.
If possible, could you share a screenshot of your data tables?
Use Merge as new from Merge Queries in Power query editor,
1.
2. Do join kind inner,
3. You will get your output,
Thanks,
Arul
Hi, so it seems with the specific information I'm using, that although I can go in and see that there are 100% matches from each column, when I try to merge it shows as no matches
