Forum Discussion
Adding columns with group by slows refresh
What format are the files?
It they are XLSX, do you have the option to convert them to CSV? CSV performs much better.
Do you have the option to put them somewhere online so the gateway isn't needed? SharePoint or similar? (no guarantees this will help but it removes a potential bottleneck.)
It is also possible to setup incremental refresh for files to ensure later refreshes are faster.
let
Source = Folder.Files("C:\SeasonsBI\SalesData"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (4)", each #"Transform File (4)"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File (4)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (4)", Table.ColumnNames(#"Transform File (4)"(#"Sample File (4)"))),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Table Column1",{{"Profit", "Whole Profit"},{"StoreID", "Store"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{ {"Store", type text}, {"TransDate", type date}, {"TransTime", type time}, {"Basket ID", type text},
{"Trans Type", type text}, {"Unit Quantity", type number}, {"Net Sale Amount", type number}, {"Gross Sale Amount", type number},
{"Net Sales Amount with TAX", type number}, {"Discounted", type logical}, {"Unit Cost", type number}, {"Unit Price", type number},
{"Code", type text}, {"Cashier", type text}, {"CustomerID", type text}, {"Whole Profit", type number}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","Exp_Lwrc","Exp_Lawrence",Replacer.ReplaceText,{"Store"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Exp_WDMR","Exp_Woodmere",Replacer.ReplaceText,{"Store"}),
#"Grouped Rows" = Table.Group(#"Replaced Value1", {"Store", "TransDate", "Trans Type", "Unit Cost", "Unit Price", "Code", "Pay Type"}, {{"Unit Quantity", each List.Sum([Unit Quantity]), type nullable number}, {"Net Sale Amount", each List.Sum([Net Sale Amount]), type nullable number}, {"Whole Profit", each List.Sum([Whole Profit]), type nullable number}})
in
#"Grouped Rows"
Super UserWhat format are the files?
It they are XLSX, do you have the option to convert them to CSV? CSV performs much better.
Do you have the option to put them somewhere online so the gateway isn't needed? SharePoint or similar? (no guarantees this will help but it removes a potential bottleneck.)
It is also possible to setup incremental refresh for files to ensure later refreshes are faster.
Thank you so much for your time and useful suggestions! In the end I upgraded to Preimum for a quick fix since my clients needed the report up and running right away.