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.
Super UserImpossible to answer with the info provided.
Can you share some detail (screenshots) of your data model?
How many rows?
Have you removed all unneeded columns?
Can you share some sample data?
Have you used any external tools to analyze the model? (you could start with Bravo)
Hello, thank you for your response!
Locally on my desktop there are 10.3 million rows which refresh nicerly.
The issue I am dealing with is when I publish the report to PowerBI service and try to refresh the data via a gateway set up that runs on a remote server, I get an error. If I do not group my data, the error message reads "There is too much data on the gateway" So I grouped my data to fix this as seen in the screenshot below, but noticed each column I add in the bottom section of the groupby adds 30 minutes to my refresh time in service. (I added one column at a time and noted how long it took to refresh the data) Adding a 4th column took over two hours which is the limit for pro users which is the license I am using, so I get a timeout error.
I did not specifically remove all unneeded columns but I grouped the data on all relevent columns so the columns not included in the groupby are gone.
I did not use external tools to analyze my model since the error is happening in Service, but I would assume a quicker refresh in Desktop = quicker refresh in service.
This table does not interact with any other tables so this is pretty much my data model, and I can create dummy data it that would be helpful.
Here is a screenshot of my "Advanced Editor".
Thank you so much!
- KNP
Sorry, those screenshots are too small to read even when I zoom.
Can you post the code as text from the advanced editor?
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"
