Power Query in Excel refreshing after small changes in queries and very slow

[Vaslo]

All,

If I even change a name of a query or something in the last step, the query refreshes all previous queries and it's very slow. Is there a way to stop this behavior that doesn't result in me just completely forgetting to refresh older ones? It's 3-4 minutes

I have a few joins which make things pretty slow. I tried to use Table.Buffer but I can't seem to find a good place to put it to improve things. Searches online are not helping me with proper placement of the Table.Buffer. Also, I am pulling from CSV or Excel files to build the PQ.

This is general because maybe there is a general fix, but I can provide more detail if necessary. This is the last query in the chain of queries. Thanks in advance.

let
    Source = #"Join Pricing Data",
    #"Removed Columns" = Table.RemoveColumns(Source,{"Sales Invoice Number", "Invoice Actual Ship Date", "Invoiced Date", "Analytics Mfg Source", "Analytics Mfg Source Description", "Fill Code", "Product GL Class and Description", "Product Description", "Conversion Factor to GAL", "Conversion Factor to LB", "Sales Class Description", "BPA", "Net Invoiced Amount at Doc", "Net Invoiced Amount at AFX", "Ext Total STD Cost at Document Rate", "Ext Raw Material STD Cost at Document Rate", "Ext Packaging STD Cost at Document Rate", "Ext Warehouse STD Cost at Document Rate", "Ext Fixed Conversion STD Cost at Document Rate", "Ext Variable Conversion STD Cost at Document Rate", "Ext COSR STD Cost at Document Rate", "Ext Total Actual Cost at Document Rate", "Ext Raw Material Actual Cost at Document Rate", "Ext Packaging Actual Cost at Document Rate", "Ext Warehouse Actual Cost at Document Rate", "Ext Fixed Conversion Actual Cost at Document Rate", "Ext Variable Conversion Actual Cost at Document Rate", "Ext COSR Actual Cost at Document Rate", "Net Quantity", "Average Rate", "Budget Rate", "Local Exchange Rate", "Corp Exchange Rate", "Local Currency Code", "Document Currency Code","Product Number", "Ship To Customer GL Class", "Account Category Code", "Fiscal Year"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Fiscal Month"]), "Fiscal Month", "Net Quantity in GAL", List.Sum),
    #"Converted null volumes to 0" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"2017 / 01", "2017 / 02", "2017 / 03", "2017 / 04", "2017 / 05", "2017 / 06", "2017 / 07", "2017 / 08", "2017 / 09", "2017 / 10", "2017 / 11", "2017 / 12", "2018 / 01", "2018 / 02", "2018 / 03", "2018 / 04", "2018 / 05", "2018 / 06", "2018 / 07", "2018 / 08", "2018 / 09", "2018 / 10", "2018 / 11", "2018 / 12", "2019 / 01", "2019 / 02", "2019 / 03", "2019 / 04", "2019 / 05", "2019 / 06"}),
    #"Temp Row for removing small Vol" = Table.AddColumn(#"Converted null volumes to 0", "TempColumnForRemovingSmallVolumes", each [#"2017 / 01"]+[#"2017 / 02"]+[#"2017 / 03"]+[#"2017 / 04"]+[#"2017 / 05"]+[#"2017 / 06"]+[#"2017 / 07"]+[#"2017 / 08"]+[#"2017 / 09"]+[#"2017 / 10"]+[#"2017 / 11"]+[#"2017 / 12"]+[#"2018 / 01"]+[#"2018 / 02"]+[#"2018 / 03"]+[#"2018 / 04"]+[#"2018 / 05"]+[#"2018 / 06"]+[#"2018 / 07"]+[#"2018 / 08"]+[#"2018 / 09"]+[#"2018 / 10"]+[#"2018 / 11"]+[#"2018 / 12"]+[#"2019 / 01"]+[#"2019 / 02"]+[#"2019 / 03"]+[#"2019 / 04"]+[#"2019 / 05"]),
    #"Filtered TempRow for Gal less than 200" = Table.SelectRows(#"Temp Row for removing small Vol", each [TempColumnForRemovingSmallVolumes] > 200),
    #"Added Repeating Region Code" = Table.AddColumn(#"Filtered TempRow for Gal less than 200", "RegionCode", each "NAPR"),
    #"Added Repeating Currency Code" = Table.AddColumn(#"Added Repeating Region Code", "CurrencyCode", each "USD"),
    #"Added Repeating Calendar Basis" = Table.AddColumn(#"Added Repeating Currency Code", "CalendarBasis", each "Fiscal"),
    #"Added Repeating Tier Adder Holder" = Table.AddColumn(#"Added Repeating Calendar Basis", "TierAdder", each 0),
    #"Added Repeating Placeholder Cust Number" = Table.AddColumn(#"Added Repeating Tier Adder Holder", "CustHold", each 0),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Repeating Placeholder Cust Number",{"ComboValue", "RegionCode", "Company Code", "Profit Center Code", "Ship-To Customer Number", "CustHold", "CustomerGroup", "Customer Name", "Ship To City", "Base Product Number", "FillType", "Base UOM", "CalendarBasis", "NewPrice", "TierAdder", "CurrencyCode", "2017 / 01", "2017 / 02", "2017 / 03", "2017 / 04", "2017 / 05", "2017 / 06", "2017 / 07", "2017 / 08", "2017 / 09", "2017 / 10", "2017 / 11", "2017 / 12", "2018 / 01", "2018 / 02", "2018 / 03", "2018 / 04", "2018 / 05", "2018 / 06", "2018 / 07", "2018 / 08", "2018 / 09", "2018 / 10", "2018 / 11", "2018 / 12", "2019 / 01", "2019 / 02", "2019 / 03", "2019 / 04", "2019 / 05", "2019 / 06", "TempColumnForRemovingSmallVolumes"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"FillTypeLetter", "TempColumnForRemovingSmallVolumes"})
in
    #"Removed Columns1"

 

[cyborgski]

Hi,
Most of my transforms are Excel and CSV. I have not found that Table.Buffer will always improve performance so I use it judiciously. And when I do use them, I put them at the end of my query.

let
...,
in Table.Buffer(LastLineOfMyQuery)

FYI, I have found the article below from Chris Webb to be more useful. I typically group my query at the end. It puts a key on the field AND has the added benefit of ensuring that my data will not duplicate my records on the merge. Because Group is an intensive operation, I might end with the Buffer.
https://blog.crossjoin.co.uk/2018/0...-power-bi-and-excel-power-query-gettransform/

Finally, try using .csv more then Excel. PQ works a lot faster on csv.

Good luck and let me know if these suggestions help.

Regards,
Mike

 

[cyborgski]

Also, I hope you don't mind the following suggestion. Your code will need to be maintained for each new period that is added. To prevent this maintenance and make your code more dynamic, I would replace the lines:

#"Converted null volumes to 0" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"2017 / 01", "2017 / 02", "2017 / 03", "2017 / 04", "2017 / 05", "2017 / 06", "2017 / 07", "2017 / 08", "2017 / 09", "2017 / 10", "2017 / 11", "2017 / 12", "2018 / 01", "2018 / 02", "2018 / 03", "2018 / 04", "2018 / 05", "2018 / 06", "2018 / 07", "2018 / 08", "2018 / 09", "2018 / 10", "2018 / 11", "2018 / 12", "2019 / 01", "2019 / 02", "2019 / 03", "2019 / 04", "2019 / 05", "2019 / 06"}),
#"Temp Row for removing small Vol" = Table.AddColumn(#"Converted null volumes to 0", "TempColumnForRemovingSmallVolumes", each [#"2017 / 01"]+[#"2017 / 02"]+[#"2017 / 03"]+[#"2017 / 04"]+[#"2017 / 05"]+[#"2017 / 06"]+[#"2017 / 07"]+[#"2017 / 08"]+[#"2017 / 09"]+[#"2017 / 10"]+[#"2017 / 11"]+[#"2017 / 12"]+[#"2018 / 01"]+[#"2018 / 02"]+[#"2018 / 03"]+[#"2018 / 04"]+[#"2018 / 05"]+[#"2018 / 06"]+[#"2018 / 07"]+[#"2018 / 08"]+[#"2018 / 09"]+[#"2018 / 10"]+[#"2018 / 11"]+[#"2018 / 12"]+[#"2019 / 01"]+[#"2019 / 02"]+[#"2019 / 03"]+[#"2019 / 04"]+[#"2019 / 05"]),


with the lines
SumColumns = List.Buffer(List.Select(Table.ColumnNames(#"Pivoted Column"), each Text.StartsWith(_,"2"))),
#"Temp Row for removing small Vol" = Table.AddColumn(#"Pivoted Column", "TempColumnForRemovingSmallVolumes", each List.Sum(Record.ToList(Record.SelectFields(_,SumColumns )))),

To use this approach requires that your sum wants to total everything starting with a 2.

Regards,
Mike

 

[Vaslo]

Also, I hope you don't mind the following suggestion. Your code will need to be maintained for each new period that is added. To prevent this maintenance and make your code more dynamic, I would replace the lines:

#"Converted null volumes to 0" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"2017 / 01", "2017 / 02", "2017 / 03", "2017 / 04", "2017 / 05", "2017 / 06", "2017 / 07", "2017 / 08", "2017 / 09", "2017 / 10", "2017 / 11", "2017 / 12", "2018 / 01", "2018 / 02", "2018 / 03", "2018 / 04", "2018 / 05", "2018 / 06", "2018 / 07", "2018 / 08", "2018 / 09", "2018 / 10", "2018 / 11", "2018 / 12", "2019 / 01", "2019 / 02", "2019 / 03", "2019 / 04", "2019 / 05", "2019 / 06"}),
#"Temp Row for removing small Vol" = Table.AddColumn(#"Converted null volumes to 0", "TempColumnForRemovingSmallVolumes", each [#"2017 / 01"]+[#"2017 / 02"]+[#"2017 / 03"]+[#"2017 / 04"]+[#"2017 / 05"]+[#"2017 / 06"]+[#"2017 / 07"]+[#"2017 / 08"]+[#"2017 / 09"]+[#"2017 / 10"]+[#"2017 / 11"]+[#"2017 / 12"]+[#"2018 / 01"]+[#"2018 / 02"]+[#"2018 / 03"]+[#"2018 / 04"]+[#"2018 / 05"]+[#"2018 / 06"]+[#"2018 / 07"]+[#"2018 / 08"]+[#"2018 / 09"]+[#"2018 / 10"]+[#"2018 / 11"]+[#"2018 / 12"]+[#"2019 / 01"]+[#"2019 / 02"]+[#"2019 / 03"]+[#"2019 / 04"]+[#"2019 / 05"]),


with the lines
SumColumns = List.Buffer(List.Select(Table.ColumnNames(#"Pivoted Column"), each Text.StartsWith(_,"2"))),
#"Temp Row for removing small Vol" = Table.AddColumn(#"Pivoted Column", "TempColumnForRemovingSmallVolumes", each List.Sum(Record.ToList(Record.SelectFields(_,SumColumns )))),

To use this approach requires that your sum wants to total everything starting with a 2.

Regards,
Mike

Mike,

It's been a few weeks and wanted to respond. Bad news is that while I did figure out that some of the issue was due to using a network drive instead of the local, now the preview refreshes when I am trying to fix the Queries are painfully slow, and I don't know why. I tried all your suggestions but they couldn't fix the refresh issue. I'll maybe post a separate item about that.

The good news is that your second suggestion to fix those formulas was a fantastic one, and I really appreciate you taking a minute to look at my clunky code and to make such a great suggestion for me to remove that manual step which I will ultimately have to do each month when I refresh.

Regardless of continued issues, thanks again and tossed so thanks your way.