Slow refresh

mr2bart

Board Regular
Joined
Dec 18, 2014
Messages
57
Hi,

I am beginning in power Query in Excel.
I have a general issue is the speed of refresh. I have something very basic code like :
VBA Code:
    Source1 = Excel.CurrentWorkbook(){[Name="tblprofit"]}[Content],
    #"Filtered Rows1" = Table.SelectRows(Source1, each ([Sales organisation] <> null) and ([Import] = "X")),

to get Source1 which is a small table of 50 lines and 6 columns, sometimes it is immediate, sometimes it take 2-3 minutes, even more to get the refreshed data list.

I have a new labtop i7 64bit, 16GoRAM, should be enough! Is there some Excel/Windows options to fine tuned ? Did I miss something?

Thanks!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
maybe use Fast Data Load and clear cache

but this is not a full M-code :confused:
 
Upvote 0
:) already done that. still very variable.
I found that if I refresh all tables before editing query and recalculate full workbook, it seems better but not working each time.
I have on the same workbook many other queries with bigger data but not linked to this one.
Also I have deactivate this in query options :
2019-12-18 10_15_34-Query Options.png
 
Upvote 0
Hope you don't load Query Tables to the Data Model by default
open brand new Excel workbook, copy your source table there, copy M-code and use it in new workbook and see if refresh taking time
but as I said, I don't see whole M-code (you presented part only) so I can't say more
 
Upvote 0
By default this option were tilted... That was a great speed improvement.
For the code, sorry, ;) here it is:

VBA Code:
let
    Source1 = Excel.CurrentWorkbook(){[Name="tblprofit"]}[Content],
    #"Filtered Rows1" = Table.SelectRows(Source1, each ([Sales organisation] <> null) and ([Import] = "X")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Remove pastille on forecast", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type1", {"Sales organisation", "Main Division", "Sales Org Currency"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Duplicates",{{"Main Division", "Division"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns2",{"Sales organisation", "Division"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Report Group", each "GRP01"),
    #"Added Custom4" = Table.AddColumn(#"Added Custom", "Unit", each "TO"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom4", {"Division", "Report Group", "Unit"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Report Group", "Unit", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    Custom6 = Table.InsertRows(#"Unpivoted Columns1", 0, {[Report Group = #"Unpivoted Columns1"{0}[Report Group], Unit = #"Unpivoted Columns1"{0}[Unit], Attribute = #"Unpivoted Columns1"{0}[Attribute],Value = "-----",Attribute.1 = #"Unpivoted Columns1"{0}[Attribute.1],Value.1 = "-----"],[Report Group = #"Unpivoted Columns1"{0}[Report Group], Unit = #"Unpivoted Columns1"{0}[Unit], Attribute = #"Unpivoted Columns1"{0}[Attribute],Value = "*",Attribute.1 = #"Unpivoted Columns1"{0}[Attribute.1],Value.1 = "*"],[Report Group = #"Unpivoted Columns1"{0}[Report Group], Unit = #"Unpivoted Columns1"{0}[Unit], Attribute = #"Unpivoted Columns1"{0}[Attribute],Value = "-----",Attribute.1 = #"Unpivoted Columns1"{0}[Attribute.1],Value.1 = "-----"]}),
    #"Cleaned Sales Org" = Custom6,
    Source2 = Excel.CurrentWorkbook(){[Name="CG_SIOP"]}[Content],
    #"Filtered Rows2" = Table.SelectRows(Source2, each ([SIOP Customer Groups] <> null)),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows2", "Report Group", each "GRP02"),
    #"Added Custom5" = Table.AddColumn(#"Added Custom1", "Unit", each "TO"),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom5",{{"SIOP Customer Groups", "SIOP Customer Group"}}),
    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"Report Group", "Unit"}, "Attribute", "Value"),
    Custom5 = Table.InsertRows(#"Unpivoted Columns2", 0, {[Report Group = #"Unpivoted Columns2"{0}[Report Group], Unit = #"Unpivoted Columns2"{0}[Unit], Attribute = #"Unpivoted Columns2"{0}[Attribute],Value = "-----"],[Report Group = #"Unpivoted Columns2"{0}[Report Group], Unit = #"Unpivoted Columns2"{0}[Unit], Attribute = #"Unpivoted Columns2"{0}[Attribute],Value = "*"],[Report Group = #"Unpivoted Columns2"{0}[Report Group], Unit = #"Unpivoted Columns2"{0}[Unit], Attribute = #"Unpivoted Columns2"{0}[Attribute],Value = "-----"]}),
    #"Cleaned CG" = Custom5,
    Source3 = Excel.CurrentWorkbook(){[Name="SIOPMatGroup"]}[Content],
    #"Filtered Rows3" = Table.SelectRows(Source3, each ([SIOP MatGroup] <> null)),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows3", "Report Group", each "GR03"),
    #"Added Custom6" = Table.AddColumn(#"Added Custom2", "Unit", each "TO"),
    #"Unpivoted Columns3" = Table.UnpivotOtherColumns(#"Added Custom6", {"Report Group", "Unit"}, "Attribute", "Value"),
    Custom1 = Table.InsertRows(#"Unpivoted Columns3", 0, {[Report Group = #"Unpivoted Columns3"{0}[Report Group], Unit = #"Unpivoted Columns3"{0}[Unit], Attribute = #"Unpivoted Columns3"{0}[Attribute],Value = "-----"]}),
    #"Cleaned MG" = Custom1,
    Source4 = Excel.CurrentWorkbook(){[Name="Focus_list"]}[Content],
    #"Removed Other Columns1" = Table.SelectColumns(Source4,{"Key Focus"}),
    #"Removed Duplicates1" = Table.Distinct(#"Removed Other Columns1"),
    #"Filtered Rows4" = Table.SelectRows(#"Removed Duplicates1", each [Key Focus] <> null),
    #"Added Custom3" = Table.AddColumn(#"Filtered Rows4", "Report Group", each "GR04"),
    #"Added Custom7" = Table.AddColumn(#"Added Custom3", "Unit", each "QTY"),
    #"Unpivoted Columns4" = Table.UnpivotOtherColumns(#"Added Custom7", {"Report Group", "Unit"}, "Attribute", "Value"),
    Custom2 = Table.InsertRows(#"Unpivoted Columns4", 0, {[Report Group = #"Unpivoted Columns4"{0}[Report Group], Unit = #"Unpivoted Columns4"{0}[Unit], Attribute = #"Unpivoted Columns4"{0}[Attribute],Value = "-----"]}),
    #"Cleaned Focus" = Custom2,
    #"Appended Query" = Table.Combine({#"Cleaned Sales Org", #"Cleaned CG", #"Cleaned MG", #"Cleaned Focus"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Appended Query",{"Report Group", "Attribute.1", "Value.1", "Attribute", "Value", "Unit"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Reordered Columns","","",Replacer.ReplaceValue,{"Attribute.1"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3",null,each [Attribute],Replacer.ReplaceValue,{"Attribute.1"}),
    Custom4 = Table.ReplaceValue(#"Replaced Value4",null,each [Value],Replacer.ReplaceValue,{"Value.1"}),
    Final = Custom4
in
    Final
 
Upvote 0
with this code PQ calculate all sources not only Source1
maybe try Table.Buffer in place you think it's taking time
 
Upvote 0
will try and keep updated. I may forgot the precise something :oops:: Please note that the refresh is long only when I edit the query, for example, this query is already loaded into a worksheet, then I want to modify my query, I click on "Source1" line and the refresh to get the data could be very long. That's why initially I show only the first 2 lines of the query, because the first line could take 2-3 minutes to get the preview.:sleep:
 
Upvote 0
I saw you read my replies in another thread so if any advice there or here doesn't work maybe think about rebuild your queries from the beginning
 
Upvote 0
I may forgot the precise something
in this case you may check your Graphic Card, how many programms are loaded into memory, clean Temp folders, check swapfile and so on... but I think this is individual case so hard to say without direct access to the machine. (I never met something like this on my computers, people usually want to turn-off preview :))
 
Last edited:
Upvote 0
I may got it !
I worked long time on Query1 without any downtime issues. Then I did a Query2 which calls Query1. Still OK. But once I want to modify Query1, I have some downtime for refresh, especially functions like = Table.ColumnNames. Strange anyway... I will have a look on windows/excel cache/memory/graphic card management. Thanks for your feedback..
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top