Custom Functions and issues with extreme long load time.

[MacJasem]

Hi guys

I've previously created and worked with custom functions that i invoke on a grouped by Year-Date column. This has previously work very fine and reduced load time very well. Now i'm creating the same report but with more steps inside the custom function and the loading fails.

- What i do inside my custom fuction is,
1. I load data from source,
2. Filter the data
3. Sum the filtered data (amount column)
4. Give the summed amount a new name in the Name Columns
5. Append the values to the source table again, where new values match column values in the source.

This is done several times in the custom function with some other minor steps. This approach has previously worked fine, but is now slowing the load time down extremely for hours until it either fails or times out.

Are there any limits (from Microsoft) on this approach or is it an approach that could be enhanced otherwise?

[MacJasem]

Thanks @Anonymous 

Here's an example of the code.

Where i get my raw data, filter the data, sum the filtered data and add some extra columns and lastly i then append the summed data to the raw data. This approach gives me the desired result i'm looking for in the desktop preview, but when i apply the changes/steps, the load just keeps on for hours. What am i missing?

(File) =>
let
Source = (File),

// Function to transform data based on a list of codes and parameters, and append the result to the source data
TransformGroup = (source as table, codes as list, newCode as text, newAccount as number, newDescription as text) as table =>

let
FilteredRows = Table.SelectRows(source, each List.Contains(codes, [Code1])),
GroupedRows = Table.Group(FilteredRows, {"Date"}, {{"Amount", each List.Sum([Amount]), type nullable number}}),
AddedColumns = Table.AddColumn(GroupedRows, "Code1", each newCode),
AddedCode2 = Table.AddColumn(AddedColumns, "Code2", each newCode),
AddedAccount = Table.AddColumn(AddedCode2, "Account_No", each newAccount),
AddedCompany = Table.AddColumn(AddedAccount, "Company", each "Company XYZ"),
AddedDescription = Table.AddColumn(AddedCompany, "Description", each newDescription),
ChangedType = Table.TransformColumnTypes(AddedDescription, {{"Account_No", Int64.Type}, {"Code1", type text}, {"Code2", type text}, {"Company", type text}}),
AppendedResults = Table.Combine({source, ChangedType})
in
AppendedResults,

// Process each group and keep appending to source
B100 = TransformGroup(Source, {"B10", "B20"}, "B100", 1, "Revenue"),
B200 = TransformGroup(B100, {"B50", "B100"}, "B200", 2, "Gross Profit"),
B300 = TransformGroup(B200, {"B150", "B200"}, "B300", 3, "Operating Profit"),
B400 = TransformGroup(B300, {"B250", "B300"}, "B400", 4, "Net Profit"),

D100 = TransformGroup(B400, {"D10", "D20", "D30"}, "D100", 10, "Current Assets"),
D200 = TransformGroup(D100, {"D50", "D100"}, "D200", 11, "Total Assets"),
D300 = TransformGroup(D200, {"D90", "D100"}, "D300", 12, "Liabilities"),
D400 = TransformGroup(D300, {"D200", "D300"}, "D400", 13, "Equity and Liabilities"),

Result = D400

in
Result

[Anonymous]

Hi @MacJasem ,

Performance issues due to increased complexity of custom functions.

You can try these steps to find out the solution:

1. Try to batch process data instead of processing it row by row. This can significantly reduce the number of operations and improve performance.
2. Make sure your function does not consume too much memory. Large datasets can cause memory problems that can lead to performance degradation or timeouts.

Then you can use Performance Analyzer to examine the Performance:

If these optimizations don't solve the problem, you may want to consider breaking the custom function into smaller, more manageable parts.

And how many more questions can I ask you:

1. What functions do you use for your custom functions?
2. Recently, there has been an update to the desktop version?

Due to security reasons, we do not support Teams and phone calls, I recommend that you share the example directly as a screenshot below the post, or share a pbix file with no sensitive data, thank you.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MacJasem]

I know some examples would be better for you guys to respond to, but it's too much to post here.

I would be happy to Show-And-Tell on a teams call if anyone's interested in helping that way - send me a PM if so.