We have the following test file: PowerQueryTestConnections.xlsx.zip (20.1 KB)
This file contains 8 queries, each with a different PowerQuery definition. We found that for 4 connections, the property PowerQueryFormula is missing (null instead of the actual value). And for 1 connection, the PowerQueryFormula.FormulaDefinition is incorrect.
Here is the corresponding test code, with indications where the errors occur:
var workbook = new Workbook("PowerQueryTestConnections.xlsx");
var connection = workbook.DataConnections[0];
var powerQueryName = connection.PowerQueryFormula?.Name; // error: null, expected: end_time
var powerQueryFormula = connection.PowerQueryFormula?.FormulaDefinition; // error: null, expected: not null
connection = workbook.DataConnections[1];
powerQueryName = connection.PowerQueryFormula?.Name; // error: null, expected: fill_url
powerQueryFormula = connection.PowerQueryFormula?.FormulaDefinition; // error: null, expected: not null
connection = workbook.DataConnections[2];
powerQueryName = connection.PowerQueryFormula?.Name;
powerQueryFormula = connection.PowerQueryFormula?.FormulaDefinition; // error: too many lines (last 4 lines belong to 'fill_url')
connection = workbook.DataConnections[3];
powerQueryName = connection.PowerQueryFormula?.Name;
powerQueryFormula = connection.PowerQueryFormula?.FormulaDefinition;
connection = workbook.DataConnections[4];
powerQueryName = connection.PowerQueryFormula?.Name;
powerQueryFormula = connection.PowerQueryFormula?.FormulaDefinition;
connection = workbook.DataConnections[5];
powerQueryName = connection.PowerQueryFormula?.Name; // expected: limit
powerQueryFormula = connection.PowerQueryFormula?.FormulaDefinition; // expected: not null
connection = workbook.DataConnections[6];
powerQueryName = connection.PowerQueryFormula?.Name; // expected: start_time
powerQueryFormula = connection.PowerQueryFormula?.FormulaDefinition; // expected: not null
connection = workbook.DataConnections[7];
powerQueryName = connection.PowerQueryFormula?.Name;
powerQueryFormula = connection.PowerQueryFormula?.FormulaDefinition;
You can see the expected outcome in Excel, by going to Data > Queries & Connections. For each query, right click > Edit and then go to ‘Advanced Editor’ to see the actual Power Query code.
Thank you for investigating and we hope you can resolve the invalid properties. We are using Aspose.Cells 24.10.0 for .NET.