Power Query Rules for Column Expansion

[willayd]

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?

[lbendlin]

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.

[MattAllington · 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.