Exception while reading PowerQuery

Aspose.Cells Product Family
1

We have the following sample file: PowerQuery1.xlsx.zip (23.9 KB)

This file contains one query. Now we have the following test code:

var workbook = new Workbook("PowerQuery1.xlsx");
var queries = workbook.DataMashup.PowerQueryFormulas;

This results in a System.NullReferenceException: ‘Object reference not set to an instance of an object.’

We are using Aspose.Cells 25.1.2 for .NET. Can you investigate and resolve this error?

2

@perfectxl

The System.NullReferenceException you are encountering when trying to access workbook.DataMashup.PowerQueryFormulas suggests that the DataMashup property might not be initialized or the workbook does not contain any Power Query formulas.

Here are a few steps you can take to troubleshoot and resolve this issue:

  1. Check if the Workbook Contains Power Query Formulas: Ensure that the PowerQuery1.xlsx file actually contains Power Query formulas. If the file is empty or does not have any queries, the DataMashup property may not be initialized, leading to a null reference.

  2. Verify the File Path: Make sure that the file path you are using to load the workbook is correct. If the file is not found, the workbook object will not be properly initialized.

  3. Update Aspose.Cells: You are using version 25.1.2 of Aspose.Cells. Consider updating to the latest version, as there may have been bug fixes or improvements related to Power Query handling in newer releases.

  4. Sample Code for Accessing Power Query Formulas: Here is a sample code snippet that demonstrates how to access Power Query formulas safely:

    var workbook = new Workbook("PowerQuery1.xlsx");
if (workbook.DataMashup != null)
{
    var queries = workbook.DataMashup.PowerQueryFormulas;
    if (queries != null)
    {
        foreach (var query in queries)
        {
            Console.WriteLine("Query Name: " + query.Name);
        }
    }
    else
    {
        Console.WriteLine("No Power Query formulas found.");
    }
}
else
{
    Console.WriteLine("DataMashup is not initialized.");
}

This code checks if the DataMashup property is initialized and if there are any Power Query formulas before attempting to access them.

If you continue to experience issues, please provide more details about the contents of the PowerQuery1.xlsx file or any additional error messages you may encounter.

3

@perfectxl,

Thanks for the template Excel file.

After initial testing, I am able to reproduce the issue as you mentioned by using your template Excel file. I found an exception “System.NullReferenceException: ‘Object reference not set to an instance of an object’” while reading PowerQuery from the file.

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-57738

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.

7

@perfectxl
It’s very strange that groups are not stored as level by level. I have changed codes about reading groups. We will include the fix into the next version 25.2.

8

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