Removed steps introduces a run out of memory error - am I missing something?

[GeorgeColl]

Hi,

I am trying to optimise some queries in a report, there appear to be a number of steps that aren't necessary so I have removed them (and I can't see that there is any dependence on what is being removed in any later steps). However, removing some steps from the query I am now getting "not enough memory to complete this operation", which shouldn't be the case as I haven't changed any steps and I have only removed/reduced the amount of data. Am I missing something obvious that I am inadvertently breaking?

The error is "Expression.Error: Evaluation ran out of memory and can't continue." and it happens when I try and preview  "Expanded Renamed Columns" in the amended query.

Original

let
    Source = Sql.Databases("DATABASE"),
    CCAClaims = Source{[Name="CCAClaims"]}[Data],
    dbo_CAClaimEstimate = CCAClaims{[Schema="dbo",Item="CAClaimEstimate"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(dbo_CAClaimEstimate,{"ClaimEstimateID", "Sequence", "Estimate Date", "Incurred", "Outstanding", "Paid", "PD Buffer", "OS Buffer", "ClaimID"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Other Columns",{"ClaimID"},CAClaims,{"ClaimID"},"CAClaims",JoinKind.RightOuter),
    #"Expanded CAClaims" = Table.ExpandTableColumn(#"Merged Queries", "CAClaims", {"Program", "Incurred", "ProgramID", "Insured"}, {"CAClaims.Program", "CAClaims.Incurred", "CAClaims.ProgramID", "CAClaims.Insured"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded CAClaims",{"CAClaims.ProgramID"},CAProgram,{"ProgramID"},"CAProgram",JoinKind.LeftOuter),
    #"Expanded CAProgram" = Table.ExpandTableColumn(#"Merged Queries1", "CAProgram", {"Start Date"}, {"CAProgram.Start Date"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded CAProgram", "AsAtDate", each Date.From(DateTime.LocalNow())),
    #"Inserted Year" = Table.AddColumn(#"Added Custom1", "Year", each Date.Year([CAProgram.Start Date]), Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Year",{{"Year", "UwYear"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Months", each (Date.Year([Estimate Date])-Date.Year([CAProgram.Start Date]))*12+Date.Month([Estimate Date])),
    #"Appended Query" = Table.Combine({#"Added Custom", Dummy}),
    #"Added Custom2" = Table.AddColumn(#"Appended Query", "Qtrs", each Number.RoundUp([Months]/3)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Years", each Number.RoundUp([Months]/12)),
    #"Sorted Rows" = Table.Sort(#"Added Custom3",{{"ClaimID", Order.Ascending}, {"Sequence", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Months", Int64.Type}, {"Qtrs", Int64.Type}, {"Years", Int64.Type}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Blank Rows",null,0,Replacer.ReplaceValue,{"PD Buffer", "OS Buffer"}),
    #"Added Custom4" = Table.AddColumn(#"Replaced Value", "Incurred Buffer", each [Incurred] - [PD Buffer]-[OS Buffer]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"ClaimEstimateID", "Sequence", "Estimate Date", "Incurred Buffer", "Incurred", "Outstanding", "Paid", "PD Buffer", "OS Buffer", "ClaimID", "CAClaims.Program", "CAClaims.Incurred", "CAClaims.ProgramID", "CAClaims.Insured", "CAProgram.Start Date", "AsAtDate", "UwYear", "Months", "Qtrs", "Years"}),
    #"Added Custom6" = Table.AddColumn(#"Reordered Columns", "NextSeq", each [Sequence]+1),
    Custom1 = Table.NestedJoin(#"Added Custom6",{"ClaimID", "Sequence"},#"Added Custom6",{"ClaimID", "NextSeq"},"Renamed Columns",JoinKind.LeftOuter),
    #"Expanded Renamed Columns" = Table.ExpandTableColumn(Custom1, "Renamed Columns", {"Incurred Buffer", "Incurred"}, {"Renamed Columns.Incurred Buffer", "Renamed Columns.Incurred"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Expanded Renamed Columns",null,0,Replacer.ReplaceValue,{"Renamed Columns.Incurred Buffer", "Renamed Columns.Incurred"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Replaced Value1",{{"Renamed Columns.Incurred Buffer", "NextBufferInc"}, {"Renamed Columns.Incurred", "NextInc"}}),
    #"Added Custom5" = Table.AddColumn(#"Renamed Columns1", "Mvmt", each [Incurred] - [NextInc]),
    Custom2 = Table.AddColumn(#"Added Custom5", "MvmtBuffer", each [Incurred Buffer] - [NextBufferInc]),
    #"Grouped Rows" = Table.Group(Custom2, {"ClaimID"}, {{"Final Seq", each List.Max([Sequence]), type number}, {"Rest", each _, type table}}),
    #"Added Custom7" = Table.AddColumn(#"Grouped Rows", "Max", each Table.Max([Rest], "Sequence")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom7",{"Final Seq", "Rest"}),
    #"Expanded Max" = Table.ExpandRecordColumn(#"Removed Columns", "Max", {"Incurred Buffer", "PD Buffer", "OS Buffer"}, {"Max.Incurred Buffer", "Max.PD Buffer", "Max.OS Buffer"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Max",{{"Max.Incurred Buffer", "Incurred Buffer"}, {"Max.PD Buffer", "PD Buffer"}, {"Max.OS Buffer", "OS Buffer"}})
in
    #"Renamed Columns2"

Amended 

let
    Source = Sql.Databases("DATABASE"),
    CCAClaims = Source{[Name="CCAClaims"]}[Data],
    dbo_CAClaimEstimate = CCAClaims{[Schema="dbo",Item="CAClaimEstimate"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(dbo_CAClaimEstimate,{"ClaimEstimateID", "Sequence", "Estimate Date", "Incurred", "Outstanding", "Paid", "PD Buffer", "OS Buffer", "ClaimID"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "AsAtDate", each Date.From(DateTime.LocalNow())),
    #"Appended Query" = Table.Combine({#"Added Custom1", Dummy}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"ClaimID", Order.Ascending}, {"Sequence", Order.Ascending}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Sorted Rows", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Blank Rows",null,0,Replacer.ReplaceValue,{"PD Buffer", "OS Buffer"}),
    #"Added Custom4" = Table.AddColumn(#"Replaced Value", "Incurred Buffer", each [Incurred] - [PD Buffer]-[OS Buffer]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"ClaimEstimateID", "Sequence", "Estimate Date", "Incurred Buffer", "Incurred", "Outstanding", "Paid", "PD Buffer", "OS Buffer", "ClaimID", "CAClaims.Program", "CAClaims.Incurred", "CAClaims.ProgramID", "CAProgram.Start Date", "AsAtDate"}),
    #"Added Custom6" = Table.AddColumn(#"Reordered Columns", "NextSeq", each [Sequence]+1),
    Custom1 = Table.NestedJoin(#"Added Custom6",{"ClaimID", "Sequence"},#"Added Custom6",{"ClaimID", "NextSeq"},"Renamed Columns",JoinKind.LeftOuter),
    #"Expanded Renamed Columns" = Table.ExpandTableColumn(Custom1, "Renamed Columns", {"Incurred Buffer", "Incurred"}, {"Renamed Columns.Incurred Buffer", "Renamed Columns.Incurred"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Expanded Renamed Columns",null,0,Replacer.ReplaceValue,{"Renamed Columns.Incurred Buffer", "Renamed Columns.Incurred"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Replaced Value1",{{"Renamed Columns.Incurred Buffer", "NextBufferInc"}, {"Renamed Columns.Incurred", "NextInc"}}),
    #"Added Custom5" = Table.AddColumn(#"Renamed Columns1", "Mvmt", each [Incurred] - [NextInc]),
    Custom2 = Table.AddColumn(#"Added Custom5", "MvmtBuffer", each [Incurred Buffer] - [NextBufferInc]),
    #"Grouped Rows" = Table.Group(Custom2, {"ClaimID"}, {{"Final Seq", each List.Max([Sequence]), type number}, {"Rest", each _, type table}}),
    #"Added Custom7" = Table.AddColumn(#"Grouped Rows", "Max", each Table.Max([Rest], "Sequence")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom7",{"Final Seq", "Rest"}),
    #"Expanded Max" = Table.ExpandRecordColumn(#"Removed Columns", "Max", {"Incurred Buffer", "PD Buffer", "OS Buffer"}, {"Max.Incurred Buffer", "Max.PD Buffer", "Max.OS Buffer"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Max",{{"Max.Incurred Buffer", "Incurred Buffer"}, {"Max.PD Buffer", "PD Buffer"}, {"Max.OS Buffer", "OS Buffer"}})
in
    #"Renamed Columns2"

[mtayyab07]

The amended code can be further improved...e.g. deduplicating data before self-joins, reducing the number of columns and rows prior to heavy operations, and filtering unnecessary data early.

Can you try below one to see if that works?

let
    Source = Sql.Databases("DATABASE"),
    CCAClaims = Source{[Name="CCAClaims"]}[Data],
    dbo_CAClaimEstimate = CCAClaims{[Schema="dbo",Item="CAClaimEstimate"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(dbo_CAClaimEstimate,{"ClaimEstimateID", "Sequence", "Estimate Date", "Incurred", "Outstanding", "Paid", "PD Buffer", "OS Buffer", "ClaimID"}),
    
    // Optimization: Early column selection
    #"Kept Essential Columns" = Table.SelectColumns(#"Removed Other Columns",{"ClaimID", "Sequence", "Incurred", "PD Buffer", "OS Buffer"}),
    
    #"Added Custom1" = Table.AddColumn(#"Kept Essential Columns", "AsAtDate", each Date.From(DateTime.LocalNow())),
    #"Appended Query" = Table.Combine({#"Added Custom1", Dummy}),
    
    // Critical optimization: Deduplication before sorting
    #"Deduplicated Data" = Table.Distinct(#"Appended Query", {"ClaimID", "Sequence"}),
    
    #"Sorted Rows" = Table.Sort(#"Deduplicated Data",{{"ClaimID", Order.Ascending}, {"Sequence", Order.Ascending}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Sorted Rows", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Blank Rows",null,0,Replacer.ReplaceValue,{"PD Buffer", "OS Buffer"}),
    #"Added Custom4" = Table.AddColumn(#"Replaced Value", "Incurred Buffer", each [Incurred] - [PD Buffer]-[OS Buffer]),
    
    // Optimization: Reduce columns before self-join
    #"Pre-Join Reduction" = Table.SelectColumns(#"Added Custom4",{"ClaimID", "Sequence", "Incurred Buffer", "Incurred"}),
    #"Added NextSeq" = Table.AddColumn(#"Pre-Join Reduction", "NextSeq", each [Sequence]+1),
    
    // Optimized self-join with deduplicated keys
    Custom1 = Table.NestedJoin(
        #"Added NextSeq", 
        {"ClaimID", "Sequence"},
        #"Added NextSeq", 
        {"ClaimID", "NextSeq"},
        "Renamed Columns",
        JoinKind.LeftOuter,
        JoinAlgorithm.SortMerge // Uses more efficient join algorithm [7]
    ),
    
    #"Expanded Renamed Columns" = Table.ExpandTableColumn(Custom1, "Renamed Columns", {"Incurred Buffer", "Incurred"}, {"Renamed Columns.Incurred Buffer", "Renamed Columns.Incurred"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Expanded Renamed Columns",null,0,Replacer.ReplaceValue,{"Renamed Columns.Incurred Buffer", "Renamed Columns.Incurred"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Replaced Value1",{{"Renamed Columns.Incurred Buffer", "NextBufferInc"}, {"Renamed Columns.Incurred", "NextInc"}})
in
    #"Renamed Columns1"

[GeorgeColl]

@mtayyab07 

I hope you had a good Easter period

I have tried the code provided and unfortunately I'm getting the below error when trying to preview "Custom 1".  I've tried playing around with it slightly to try and fix but given the step before appears to be working I'm not sure where it's finding the list from in that step

Expression.Error: We cannot convert the value 2 to type List.
Details:
    Value=2
    Type=Type

[v-menakakota]

Hi @GeorgeColl ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

Thanks for giving that a try! The error message indicates that Power Query is expecting a list but is getting a single value (likely during the self-join). This usually happens when there’s a mismatch in types or a join behaves unexpectedly.

Try removing the JoinAlgorithm.SortMerge from the Table.NestedJoin step and re-run this may resolve the issue.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a Kudos.

[GeorgeColl]

Hi @v-menakakota 

I removed the sortmerge line and managed to run that step (although it was very slow). However, the next step "Expanded Renamed Columns" is now giving the evaluation ran out of memory and can't continue error again.

I'm not having any issue with the original query, but it definitely has unnecessary step an I thought could be optimised. I can't see any reason why the amended version would be having this issue?

There are 1,316,219 rows in the data and I need to optimise the queries in the report, this one felt relatively straight forward to look at first!

[v-menakakota]

Hi @GeorgeColl ,

Thanks for confirming that! Since removing the JoinAlgorithm.SortMerge didn’t fix the issue, it looks like Power Query is still trying to expand something that’s not actually a table which is what’s causing that “cannot convert value to type List” error.
What’s likely happening is that the join isn’t returning a match for some rows, so instead of a table (which the expand step expects), it’s getting a single value like null or even just a number.
To fix this, we can add a quick step to keep only the rows where the join result actually is a table

 Perform the join
Custom1 = Table.NestedJoin(
#"Added NextSeq",
{"ClaimID", "Sequence"},
#"Added NextSeq",
{"ClaimID", "NextSeq"},
"Renamed Columns",
JoinKind.LeftOuter
),
 Keep only the rows where the join result is a table
FilteredRows = Table.SelectRows(Custom1, each Value.Is([Renamed Columns], type table)),
 Now safely expand the joined fields
#"Expanded Renamed Columns" = Table.ExpandTableColumn(
FilteredRows,
"Renamed Columns",
{"Incurred Buffer", "Incurred"},
{"Renamed Columns.Incurred Buffer", "Renamed Columns.Incurred"}
)
This should help avoid that error and let things expand smoothly.
Once try to follow this.

Thank you and Regards,
Menaka Kota.

[v-menakakota]

Hi @GeorgeColl ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[GeorgeColl]

Hi @v-menakakota,

Sorry I'm still having the same issue. The preview is just loading infinitely, when I try to load the query it's just been stuck loading data for a while...

Thanks,

[v-menakakota]

Hi @GeorgeColl ,

Since it’s still getting stuck on loading, this might be less about the logic and more about the volume of data being processed especially around the join and expand steps, which can get heavy with large datasets.

To help pinpoint whether it’s a data size or logic issue, we can temporarily limit the rows early in the query. This doesn’t fix the problem directly, but it helps us figure out if the operation would work at all on a smaller dataset kind of like a safe test run.

Right after the step where you remove other columns (or any early step), insert something like this:

#"Limited Rows" = Table.FirstN(#"PreviousStep", 1000)

Just replace "PreviousStep" with the name of your last step before this one. Then continue the rest of your steps using "Limited Rows" as the source.

Thank you and Regards,
Menaka Kota.

[GeorgeColl]

Hey @v-menakakota,

With the 1000 row limit it works very efficiently and there's no errors, so I guess it appears to be a data volume issue.

My only confusion is that if I try the original query, it takes about 3-5 minutes but it does finish and it doesn't error out but I cannot fathom why that would be the case?

[GeorgeColl · Accepted solution]

I have managed to get it running with the code below, it's removed a lot of the unneccesary parts so it will certainly be an improvement. I'm not sure why the code provided by yourselves is not working, but I'm going to mark this as a solution as I don't want to waste any more of your time as it seems to be a really odd issue.

let
Source = Sql.Databases("Database"),
CCAClaims = Source{[Name="CCAClaims"]}[Data],
dbo_CAClaimEstimate = CCAClaims{[Schema="dbo",Item="CAClaimEstimate"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(dbo_CAClaimEstimate,{"Sequence", "Incurred", "PD Buffer", "OS Buffer", "ClaimID"}),

#"Appended Query" = Table.Combine({#"Removed Other Columns", Dummy2}),
#"Sorted Rows" = Table.Sort(#"Appended Query",{{"ClaimID", Order.Ascending}, {"Sequence", Order.Ascending}}),
#"Removed Blank Rows" = Table.SelectRows(#"Sorted Rows", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Replaced Value" = Table.ReplaceValue(#"Removed Blank Rows",null,0,Replacer.ReplaceValue,{"PD Buffer", "OS Buffer"}),
#"Added Custom4" = Table.AddColumn(#"Replaced Value", "Incurred Buffer", each [Incurred] - [PD Buffer]-[OS Buffer]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Sequence", "Incurred Buffer", "Incurred", "PD Buffer", "OS Buffer", "ClaimID"}),
#"Added Custom6" = Table.AddColumn(#"Reordered Columns", "NextSeq", each [Sequence]+1),
Custom1 = Table.NestedJoin(#"Added Custom6",{"ClaimID", "Sequence"},#"Added Custom6",{"ClaimID", "NextSeq"},"Renamed Columns",JoinKind.LeftOuter),
#"Expanded Renamed Columns" = Table.ExpandTableColumn(Custom1, "Renamed Columns", {"Incurred Buffer", "Incurred"}, {"Renamed Columns.Incurred Buffer", "Renamed Columns.Incurred"}),
#"Replaced Value1" = Table.ReplaceValue(#"Expanded Renamed Columns",null,0,Replacer.ReplaceValue,{"Renamed Columns.Incurred Buffer", "Renamed Columns.Incurred"}),
#"Renamed Columns1" = Table.RenameColumns(#"Replaced Value1",{{"Renamed Columns.Incurred Buffer", "NextBufferInc"}, {"Renamed Columns.Incurred", "NextInc"}}),
#"Added Custom5" = Table.AddColumn(#"Renamed Columns1", "Mvmt", each [Incurred] - [NextInc]),
Custom2 = Table.AddColumn(#"Added Custom5", "MvmtBuffer", each [Incurred Buffer] - [NextBufferInc]),
#"Grouped Rows" = Table.Group(Custom2, {"ClaimID"}, {"Rest", each _, type table}),
#"Added Custom7" = Table.AddColumn(#"Grouped Rows", "Max", each Table.Max([Rest], "Sequence")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom7",{"Rest"}),
#"Expanded Max" = Table.ExpandRecordColumn(#"Removed Columns", "Max", {"Incurred Buffer", "PD Buffer", "OS Buffer"}, {"Max.Incurred Buffer", "Max.PD Buffer", "Max.OS Buffer"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded Max",{{"Max.Incurred Buffer", "Incurred Buffer"}, {"Max.PD Buffer", "PD Buffer"}, {"Max.OS Buffer", "OS Buffer"}})
in
#"Renamed Columns2"