cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Power Query Rules for Column Expansion

Let's say I have two tables in a database, Sales and Sales_Detail, that have a defined primary / foreign key relationship. I've noticed when pulling the Sales_Detail into Power Query that I get the option to "expand" columns from the Sales table.

 

However, if I place a view directly on top of the Sales_Detail and pull that into Power Query instead, I lose the ability to still expand columns from the Sales table. I imagine this is because Power Query doesn't introspect the view to see the relationships behind it, but is this documented anywhere or alternately is there a way to explicitly tell the Power Query editor to allow expansion of Sales columns in the view?

1 ACCEPTED SOLUTION

You can just do it manually. Load both tables in Power a Query and do a merge on the key, then you can expand from there. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

2 REPLIES 2
Super User III
Super User III

we generally advise our developers to switch off all the automatic field type and relationship detection options. Oftentimes these options lead to questionable results.

 

The developers need to know their data models, and they need to make these decisions themselves.

You can just do it manually. Load both tables in Power a Query and do a merge on the key, then you can expand from there. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors