Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
aallman
Helper II
Helper II

Combine Multiple Summary Tables into One

Hello!

 

I currently have 3 queries pulling from different sources. I have summarized each of these into summary tables that contain the ID column. All I need to do is get a comprehensive list of IDs from all 3 tables joined into one. I have looked into Union, NaturalInnerJoin, and CrossJoin but those all seem to be some variation of combining only rows that are found in all tables. I know that some of my IDs will be in all 3 tables but some will only appear in one. How can I get a final master list of IDs into one table?

 

Thanks in advance!

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@aallman assuming your 3 tables called table 1, table 2 and table 3

 

you can use following DAX to create a new tables:

 

ID Table = 
DISTINCT (
   UNION ( 
       SELECTCOLUMNS ( Table1, "Id", Table1[Id] ),
       SELECTCOLUMNS ( Table2, "Id", Table2[Id] ),
       SELECTCOLUMNS ( Table3, "Id", Table3[Id] )
   )
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

5 REPLIES 5
aallman
Helper II
Helper II

thank you this worked! I was not doing distinct and I think that was messing it up.

 

parry2k
Super User
Super User

@aallman assuming your 3 tables called table 1, table 2 and table 3

 

you can use following DAX to create a new tables:

 

ID Table = 
DISTINCT (
   UNION ( 
       SELECTCOLUMNS ( Table1, "Id", Table1[Id] ),
       SELECTCOLUMNS ( Table2, "Id", Table2[Id] ),
       SELECTCOLUMNS ( Table3, "Id", Table3[Id] )
   )
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I think he mentioned that he has tried UNION and it didnt work for him.



Regards,
Sachin
Check out my Blog

@SachinNandanwar understood but not sure how he used it, without knowing hard to tell. The expression I gave will work 100%.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

SachinNandanwar
Super User
Super User

Please provide some sample data.



Regards,
Sachin
Check out my Blog

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.