Incorrect function type and items for PowerQuery

Aspose.Cells Product Family
1

We have the following sample file: PowerQuery2.xlsx.zip (30.6 KB)

This file contains 5 queries, all functions. Now we have the following test code:

var workbook = new Workbook("PowerQuery2.xlsx");

var query1 = workbook.DataMashup.PowerQueryFormulas["Transform File (2)"];
var queryType1 = query1.Type;                      // OK - PowerQueryFormulaType.Function
var formulaItems1 = query1.PowerQueryFormulaItems; // OK - one item with key 'Source'

var query2 = workbook.DataMashup.PowerQueryFormulas["fnSmartFolder"];
var queryType2 = query2.Type;                      // NOT OK - expected PowerQueryFormulaType.Function
var formulaItems2 = query2.PowerQueryFormulaItems; // NOT OK - expected one item with key 'fnSmartFolder' (or two items 'fn' and 'fnDocumentation')

var query3 = workbook.DataMashup.PowerQueryFormulas["fnSmartFile"];
var queryType3 = query3.Type;                      // NOT OK - expected PowerQueryFormulaType.Function
var formulaItems3 = query3.PowerQueryFormulaItems; // NOT OK - expected one item with key 'fnSmartFile' (or two items 'fnSmartFile' and 'fnDocumentation')

var query4 = workbook.DataMashup.PowerQueryFormulas["fnGetParameter"];
var queryType4 = query4.Type;                      // NOT OK - expected PowerQueryFormulaType.Function
var formulaItems4 = query4.PowerQueryFormulaItems; // NOT OK - expected one item with key 'fnGetParameter' (or two items 'fnGetParameter' and 'fnDocumentation')

var query5 = workbook.DataMashup.PowerQueryFormulas["Transform File"];
var queryType5 = query5.Type;                      // OK - PowerQueryFormulaType.Function
var formulaItems5 = query5.PowerQueryFormulaItems; // OK - one item with key 'Source'

For three of the five queries, the recognized query type is incorrect (Formula instead of Function). Also, the items are incorrect and do not match the steps shown in Excel. These can be seen via Queries & Connections > Edit. The ‘f(x)’ icon for each query indicates that all are functions. The ‘Applied steps’ window shows one step for each of the functions.

Can you reproduce these invalid properties and provide a fix? We’re using Aspose.Cells 25.1.2 for .NET.

2

@perfectxl

It seems that you are encountering issues with the recognition of query types and items in your PowerQuery formulas when using Aspose.Cells. Specifically, some of your queries are being identified incorrectly as a different type than expected, and the items associated with those queries do not match what you see in Excel.

To troubleshoot this issue, you can follow these steps:

  1. Verify the Version: Ensure that you are using the latest version of Aspose.Cells. You mentioned using version 25.1.2; check if there are any updates or patches available that might address this issue.

  2. Check Query Definitions: Open the PowerQuery in Excel and verify the definitions of the queries. Make sure that they are indeed set up as functions and that there are no discrepancies in the names or configurations.

  3. Sample Code for Debugging: You can use the following sample code to iterate through the PowerQuery formulas and print their types and items. This will help you confirm what Aspose.Cells is recognizing:

    var workbook = new Workbook("PowerQuery2.xlsx");
var powerQueryFormulas = workbook.DataMashup.PowerQueryFormulas;

foreach (var query in powerQueryFormulas)
{
    Console.WriteLine($"Query Name: {query.Name}");
    Console.WriteLine($"Query Type: {query.Type}");
    foreach (var item in query.PowerQueryFormulaItems)
    {
        Console.WriteLine($"Item Key: {item.Key}, Item Value: {item.Value}");
    }
}

  4. Contact Support: If the issue persists after verifying the above steps, consider reaching out to Aspose support with your sample file and the code you are using. They may be able to provide insights or fixes specific to the version you are using.

  5. Review Documentation: Check the Aspose.Cells documentation for any notes on PowerQuery handling, as there may be specific requirements or limitations that could affect how queries are processed.

By following these steps, you should be able to identify the root cause of the discrepancies in query types and items. If you find that the issue is indeed a bug or limitation in the library, reporting it to Aspose support will help them address it in future releases.

If you have any further questions or need additional assistance, feel free to ask!

3

@perfectxl,

Thanks for the template Excel file.

After initial testing, I was able to reproduce the issue as you mentioned by using your template Excel file. I found some incorrect function type and items retrieved for PowerQuery.

We require thorough evaluation of the issue. 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-57739

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.

4

@perfectxl,

We are pleased to inform you that your issue(Ticket ID: “CELLSNET-57739”) has been resolved. The fix will be included in our upcoming release (Aspose.Cells v25.2) that we plan to release in the first half of February 2025. You will be notified when the next version is released.

5

The issues you have found earlier (filed as CELLSNET-57739) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi