Adding columns with group by slows refresh

[meirlicht]

Hello, 

When I do not group my data, I get the following error message when trying to refresh in service: "The amount of data on the gateway client has exceeded the limit for a single table. Please consider reducing the use of highly repetitive strings values through normalized keys, removing unused columns, or upgrading to Power BI Premium."

So I grouped my data, but with each new column I add in the grouping, my data refresh significantly slows down in service and I get a time out error "Before the data import for finished, its data source timed out. Double-check whether that data source can process import queries, and if it can, try again."

Any ideas of solutions/workarounds or is my only option to upgrade to Premium? 

Thank you!

[v-henryk-mstf]

Hi @meirlicht ,

Thanks for your feedback.

If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,
Henry

[KNP]

Impossible 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)

[meirlicht]

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?

[meirlicht]

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"

[KNP · Accepted solution]

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.

[meirlicht]

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.