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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
JohnFonz
Frequent Visitor

Dynamically joining distinct nested tables based on a common column

Hi guys, 

 

I am working on a dataset where I want to combine multiple nested tables into one big table. The nature of the nested tables are in the attached screenshots below; each nested table contains a "Unique" column which is common to all of them and I want the end result to contain one Unique column and all the rest of the columns from each nested table in one table.

 

I think I am right in saying that, I am trying to join all these tables dynamically.

 

I would really appreciate it if you could suggest me a way.

 

Many thanks,

John 😊

 

Example table 1Example table 1

 

Example table 2Example table 2

 

Below is a screenshot of the end goal.

 

End goalEnd goal

 

4 REPLIES 4
Anonymous
Not applicable

not completely clear what you want, but I would try this strategy:
1) expand the column of tables
2) group the data of the large table obtained on the Unique column and choose not to aggregate on any column but to keep all the rows.

 

If you attach a file with some dummy tables I can write you the single steps in detail.

I now have a sample dataset but not sure how to upload it here. Any thoughts? Thank you.

Hi @JohnFonz 

 

You can refer to this blog https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

 

Best Regards,
Community Support Team _ Jing

Hi Rocco,

Thank you for the response. 

This is a method I tried but dropped for performance reasons. Each table contains 8760 rows and the "Unique" column contains 8760 distinct values. So, when you expand, group with or without aggregation, it slowed down the query significantly. I am working on dummy tables, will update it here soon.

Many thanks

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors