Help - Report Deadline - Group By Performance is Slow (Hours!)

[Txtcher]

Hello:

We are using PQ to create a raw data table from Salesforce. (We cannot use Power BI, because the customer wants the raw data in Excel. [One of the frustrations with Power BI is the inability to export a table to Excel.])

The data is huge and we absolutely need to do aggregations. This is the original table

ParentId

CreatedDate

NewValue

M-code used to create table:

let
Source = Salesforce.Data("https://login.salesforce.com/ ", [ApiVersion=48]),
RS_Application__History = Source{[Name="RS_Application__History"]}[Data],
// Purpose to reduce the size of the table b/c we only need apps approved in FY2024
FilterCreatedDate = Table.SelectRows(RS_Application__History, each [CreatedDate] > #datetime(2019, 1, 1, 0, 0, 0)),
ExtractCreatedDate = Table.TransformColumns(FilterCreatedDate,{{"CreatedDate", DateTime.Date, type date}}),
RemoveColumns = Table.SelectColumns(ExtractCreatedDate,{"ParentId", "CreatedDate", "NewValue"}),
FilterRETypes = Table.SelectRows(RemoveColumns, each List.Contains(RE_Types,[NewValue],Comparer.OrdinalIgnoreCase)),

//Filter this list with query created to find Licensed Issued min date to obtain only NewValues related to Apps approved in FY24

FilterLicensedList = Table.SelectRows(FilterRETypes, each List.Contains(List.Buffer(LicensedIssued[ParentId]),[ParentId]))
in
FilterLicensedList

The NewValue column contains several different values which all need min/max aggregations based on date.

We created reference queries to the table above for each of the values we need to aggregate, filtered the NewValue we needed and did a GroupBy:

let
Source = #"History: Application",
#"Filtered Rows" = Table.SelectRows(Source, each ([NewValue] = "IN REVIEW")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"ParentId"}, {{"Min In Review", each List.Min([CreatedDate]), type date}, {"Max In Review", each List.Max([CreatedDate]), type date}})
in
#"Grouped Rows"

Unfortunately, this approach is taking us ages. The queries literally run for hours! We are on a deadline so any help is greatly appreciated.

[Shruti_D · Accepted solution]

Hi @Txtcher ,

As mentioned by @lbendlin , you can use Power BI's Power query to perform your transformations and use that M query in your excel.

Go to get data--> Blank query and paste it.

You will get your table
The you can just copy the table and paste it in the excel.

Another way can be,
Use table visualization to export the data.

Power BI is a very interesting tool and it has got many capabilities apart from transforming your data using Power Query.

Check this out to get started with Power BI.

https://learn.microsoft.com/en-us/power-bi/fundamentals/desktop-getting-started
Download from here

Hope this helps!
If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Txtcher]

Well....I never knew there was a way to export from Power BI to Excel. I have searched and searched and never came up with a solution to do it. Probably because why would you do that? But I work for state government. They are very behind on technology. They want everything emailed in an Excel spreadsheet. Only a handful of employees even have Power BI. 

Question: does Power Query in BI handle data aggregation differently than Excel? It is my understanding that Excel is loading everything to memory and that is why it takes so long to run.  If Power BI does it the same way, that's okay. I can set up a model and write DAX to aggregate the data as needed. I am not very experienced at it, but I can probably muddle my way through it given enough time. 

Thanks for the response.

[lbendlin]

Even though the Salesforce Object connector is not officially marked as supporting Query Folding  (the "View Native Query"  is greyed out) it does in fact support SOME folding.  Especially filters fold well.  You can verify the folding by using Query Diagnostics.

Some Power Query transforms WILL break that folding, resulting in the need to fetch the entire RS_Application__History object (with potentially millions of rows).

Use Query Diagnostics to see where the folding starts to break, and then do NOT perform the actions that cause the breakage. Instead do the groupings and column type changes etc in the Power BI data model rather than in Power Query.

The data in RS_Application__History is immutable, so you should consider setting up Incremental Refresh for this.

[Txtcher]

Thank you for your response. Query Diagnostics is unavailable for us (IT dept ). (If only Salesforce had a way to find min/max dates in the report editor. )

However, please note we can't use Power BI. The Customer wants the raw data in an Excel table and I have never discovered a way to export a table from Power BI into Excel.

[lbendlin]

Nobody can stop you from using Power BI Desktop.  You can do your diagnostics there and once you are happy with the results you can port the Power Query code over to Excel.