Forum Discussion
How to join 2 tables using DAX without losing any rows from either table
Hello,
I have 2 calculated tables that I need to merge into 1 table without losing any rows from either table. As they are calculated tables, I can't use Power Query. How can I achieve this in DAX please?
For example:
TableA:
| LinkID | Date | CompanyNumber | CompanyName | Branch Number |
| 114_11/11/2025_2 | 11/11/2025 | 114 | ABC | 2 |
| 114_11/11/2025_2 | 11/11/2025 | 114 | ABC | 2 |
| 114_11/11/2025_1 | 11/11/2025 | 114 | ABC | 1 |
TableB:
| LinkID | TestA | TestB |
| 114_11/11/2025_2 | 123 | 11 |
| 114_11/11/2025_2 | 321 | 22 |
| 114_11/11/2025_3 | 985 | 425 |
Merge A and B together to get:
| LinkID | Date | CompanyNumber | CompanyName | Branch Number | TestA | TestB |
| 114_11/11/2025_2 | 11/11/2025 | 114 | ABC | 2 | 123 | 11 |
| 114_11/11/2025_2 | 11/11/2025 | 114 | ABC | 2 | 321 | 22 |
| 114_11/11/2025_1 | 11/11/2025 | 114 | ABC | 1 | null | null |
| 114_11/11/2025_3 | null | null | null | null | 985 | 425 |
Thank you.
Anonymous To extend what ExcelMonke mentioned, you can do something like this:
Table New = --create table A VAR __T1 = SELECTCOLUMNS ( TableA, "LinkID", TableA[LinkID] & "", "Date", TableA[Date], "CompanyName", TableA[CompanyName], "CompanyNumber", TableA[CompanyNumber], "BranchNumber", TableA[Branch Number] ) --create table b VAR __T2 = SELECTCOLUMNS ( TableB, "LinkID", TableB[LinkID] & "", "TestA", TableB[TestA], "TestB", TableB[TestB] ) --get matching rows from tablea and tableb, keep only distinct rows to avoid duplicates --because of many to many VAR __T3 = DISTINCT ( NATURALLEFTOUTERJOIN ( __T1, __T2 ) ) --find out which LinkID are already part of the output VAR __T4 = DISTINCT ( SELECTCOLUMNS ( __T3, [LinkID] ) ) --get remaining ids from tableb, and add missing column with the blank values VAR __T5 = SELECTCOLUMNS ( FILTER ( __T2, NOT [LinkID] IN __T4 ), "LinkID", [LinkID], "Date", BLANK (), "CompanyName", BLANK (), "CompanyNumber", BLANK (), "BranchNumber", BLANK (), "TestA", [TestA], "TestB", [TestB] ) RETURN --combine tables to get the final output UNION ( __T3, __T5 )
5 Replies
- parry2k
Super User
Anonymous To extend what ExcelMonke mentioned, you can do something like this:
Table New = --create table A VAR __T1 = SELECTCOLUMNS ( TableA, "LinkID", TableA[LinkID] & "", "Date", TableA[Date], "CompanyName", TableA[CompanyName], "CompanyNumber", TableA[CompanyNumber], "BranchNumber", TableA[Branch Number] ) --create table b VAR __T2 = SELECTCOLUMNS ( TableB, "LinkID", TableB[LinkID] & "", "TestA", TableB[TestA], "TestB", TableB[TestB] ) --get matching rows from tablea and tableb, keep only distinct rows to avoid duplicates --because of many to many VAR __T3 = DISTINCT ( NATURALLEFTOUTERJOIN ( __T1, __T2 ) ) --find out which LinkID are already part of the output VAR __T4 = DISTINCT ( SELECTCOLUMNS ( __T3, [LinkID] ) ) --get remaining ids from tableb, and add missing column with the blank values VAR __T5 = SELECTCOLUMNS ( FILTER ( __T2, NOT [LinkID] IN __T4 ), "LinkID", [LinkID], "Date", BLANK (), "CompanyName", BLANK (), "CompanyNumber", BLANK (), "BranchNumber", BLANK (), "TestA", [TestA], "TestB", [TestB] ) RETURN --combine tables to get the final output UNION ( __T3, __T5 ) 
ExcelMonkeImpactful Individual
Hello,
Whilst the recommendation would be to do this in Power Query. Otherwise, consider using a function like CROSSJOIN or ADDCOLUMNS.
- ExcelMonke
Hello,
Whilst the recommendation would be to do this in Power Query. Otherwise, consider using a function like CROSSJOIN or ADDCOLUMNS.
- MasonMA
Hi, the easiest and most stable solution is to recreate the two tables in Power Query and perform a Full Outer Join there. If you could share the logic of how your 2 Calculated tables were created, other users would also be able to provide you with an alternative solution so that you can compare.
- parry2k
I am not sure about stable approach doing in PQ vs DAX but surely as a best practice if posdible
one should do it in PQ. Although both approaches are stable.
