Joined
·
2 Posts
I do have an excel workbook which fetches data from a Query (Call it Qx). Now, this query is linked to 150 other queries (which in turn are linked to xml files and these files changes frequently). Whenever I refresh any pivot table which is based on Qx in excel; this refresh runs Qx which in turn get refresh from 150 other queries. The combined data in Qx is ~50k rows and ~30 columns. Now the issue is that this pivot takes time to refresh (~1 minute) and sometime it crashes as well as I assume data is huge (although I have very decent machine but refresh time is very irritating)
I tried to optimize Qx by selecting only required columns (through build in editor in MS Query) but I guess it still querying all these 150 queries fully for all columns. Can anybody help in cut short refresh time? Here is Qx:
--------
let
Source = Table.Combine({1, 2, ..150"}),
#"Removed Columns" = Table.RemoveColumns(Source,{"a", "b", "c", "....25"})
in
#"Removed Columns"
---------
I tried to optimize Qx by selecting only required columns (through build in editor in MS Query) but I guess it still querying all these 150 queries fully for all columns. Can anybody help in cut short refresh time? Here is Qx:
--------
let
Source = Table.Combine({1, 2, ..150"}),
#"Removed Columns" = Table.RemoveColumns(Source,{"a", "b", "c", "....25"})
in
#"Removed Columns"
---------
