PowerQueryFormulaItems missing in function with a nested let

Aspose.Cells Product Family
1

In Aspose.Cells for .NET version 25.4.0 I found the following problem for the PowerQueryFormulaItems of a function.

When a file contains a power query function with a nested let like so:

let
    Source = {"banana", "apple", "potato", "pineapple"},
    FindFruit = (text) => let
        Text = List.FindText(Source,text)
        in Text
in
    FindFruit

only the “FindFruit” item is present in the PowerQueryFormulaItem collection of the query rather than the two items, “Source” and “FindFruit” that I would expect to see.

This file MissingItem.xlsx.zip (13.1 KB) contains the above example.

To check the current items you can use the following snippet:

var workbook = new Workbook("MissingItem.xlsx")
var query1 = workbook.DataMashup.PowerQueryFormulas["FindFruit"];
var formulaItems1 = query1.PowerQueryFormulaItems; // Incorrect, missing the "Source" item
2

@perfectxl

Thank you for providing the sample Excel file and the code snippet to reproduce this issue. Having a clear example of the nested let expression structure is very helpful for our investigation.

I have successfully reproduced the behavior you described. When parsing the Power Query formula, the PowerQueryFormulaItems collection only captures the inner scope of the nested expression, causing items from the outer scope—such as “Source” in your example—to be omitted from the collection.

Our engineering team has confirmed this is a bug in how the DataMashup engine processes nested scopes within Power Query functions. We are currently working on a fix to ensure that all formula items across both outer and inner scopes are correctly identified and populated.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-59939

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

3

@perfectxl
Please try the latest version 26.3.

4

@perfectxl,

And, you may download the Aspose.Cells for .NET v26.3 from the Releases section or NuGet repos. here:
Releases | NuGet

I hope this is helpful to you.

Let us know your feedback/results after using the Aspose.Cells for .NET v26.3.

5

It works now, thanks for the quick update!

6

@perfectxl

You are very welcome! We are glad to hear that the latest version resolved the issue with PowerQueryFormulaItems in nested let functions.

Thank you for confirming the fix and for your positive feedback. Please feel free to reach out if you encounter any other questions or need further assistance with Aspose.Cells.