I have one SQL query that takes an hour to update table, now I have created connection in Excel Power Query and based on same table have created multiple sub table, so whenever I refresh all the connections (Main Table & Sub Table) refreshes and takes more time, as same query is being executing in all the connections.
Now, i wanted to Optimize this by creating one master table (parent) and all sub tables (child) that should sync with parent table. So once master table is updated, than sub tables (summary or custom view) can be created easily as source will be internal.
Please advise if this is doable in Excel Power Query (2016 & O365)
Advertisement
Answer
One way to do this is to load the master table to a sheet and then point the sub tables at that Excel table, rather than the original source. Then you can do a two-step refresh.
- Refresh the master table.
- Refresh all the sub tables
You can group all the sub tables into one query folder that can be refreshed with one right-click > Refresh action.