Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Using Excel 365.
I created a connection to a file on a network share
I create a query that references the connection.
The query is very slow and eventually Excel locks up and I have to Exit and come back in.
What can I do to speed things up?
let
Source = SOV_Workbook,
#"Added Custom" = Table.AddColumn(Source, "Supplier_PO_Key", each [Supplier]&Number.ToText([#"PO#"])),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Supplier_PO_Key", "Division", "Region", "Project", "Supplier", "PO#", "WBS", "GL"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Supplier", Order.Ascending}, {"PO#", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Division] = "Education" or [Division] = "Seniors"))
in
#"Filtered Rows"
Thanks,
-w
Solved! Go to Solution.
@Anonymous
Since there weren't any error messages, time out may occurred.
If there are advanced operations(e.g merge, append, reference other steps, invoke custom functions...) with large data, they will cause the looping calculation and duplicate cost on memory resource, You can refer to following:
Referenced Queries And Caching In Power BI And Power Query
How and When to use List & Table Buffer?
You can also try to increase the cache limits.
1. Click Settings->Options->Global->Data Load to expose the "Data Cache Management Options".
2. Try increasing the Maximum allowed (MB):to 16000 (or about double what the current setting is)
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
@Anonymous
Since there weren't any error messages, time out may occurred.
If there are advanced operations(e.g merge, append, reference other steps, invoke custom functions...) with large data, they will cause the looping calculation and duplicate cost on memory resource, You can refer to following:
Referenced Queries And Caching In Power BI And Power Query
How and When to use List & Table Buffer?
You can also try to increase the cache limits.
1. Click Settings->Options->Global->Data Load to expose the "Data Cache Management Options".
2. Try increasing the Maximum allowed (MB):to 16000 (or about double what the current setting is)
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
You should filter before you sort. Sorting brings the whole unfiltered table into memory. Just move the Division filter to before the sort.
--Nate
Thanks Nate,
I moved the FIlter up.
Excel is still taking too long/freezing.
Other ideas?
Thanks,
-w
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.