Slow refresh

[mr2bart]

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 :

    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!

 

[sandy666]

maybe use Fast Data Load and clear cache

but this is not a full M-code

 

[mr2bart]

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 :

 

[sandy666]

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]

By default this option were tilted... That was a great speed improvement.
For the code, sorry, here it is:

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]

with this code PQ calculate all sources not only Source1
maybe try Table.Buffer in place you think it's taking time

 

[mr2bart]

will try and keep updated. I may forgot the precise something : 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.

 

[sandy666]

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]

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 )

 

[mr2bart]

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..