Slow refresh

mr2bart

Board Regular
Joined
Dec 18, 2014
Messages
56
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!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,275
maybe use Fast Data Load and clear cache

but this is not a full M-code :confused:
 

mr2bart

Board Regular
Joined
Dec 18, 2014
Messages
56
:) 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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,275
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
 

mr2bart

Board Regular
Joined
Dec 18, 2014
Messages
56
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,275
with this code PQ calculate all sources not only Source1
maybe try Table.Buffer in place you think it's taking time
 

mr2bart

Board Regular
Joined
Dec 18, 2014
Messages
56
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:
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,275
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,275
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:

mr2bart

Board Regular
Joined
Dec 18, 2014
Messages
56
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..
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,930
Messages
5,508,173
Members
408,669
Latest member
AgsikapAko

This Week's Hot Topics

Top