This file contains 8 queries. In Excel, they are listed as follows in the ‘Queries & Connections’ pane: PowerQueryFormulas list.png (10.5 KB)
In our application, we try to read the same display info as in Excel. Currently, the order of queries returned by Aspose is not the same as in Excel. Also, it is not possible to read the group of a query.
Here is some test code to demonstrate what we try to achieve:
var workbook = new Workbook("PowerQueryTestConnections.xlsx");
var powerQueryFormula = workbook.DataMashup.PowerQueryFormulas[0];
Console.WriteLine(powerQueryFormula.Name); // expected: start_time
Console.WriteLine(powerQueryFormula.Group); // not possible, expected: Fills / test
powerQueryFormula = workbook.DataMashup.PowerQueryFormulas[1];
Console.WriteLine(powerQueryFormula.Name); // expected: end_time
Console.WriteLine(powerQueryFormula.Group); // not possible, expected: Fills / test
powerQueryFormula = workbook.DataMashup.PowerQueryFormulas[2];
Console.WriteLine(powerQueryFormula.Name); // expected: limit
Console.WriteLine(powerQueryFormula.Group); // not possible, expected: Fills / test
powerQueryFormula = workbook.DataMashup.PowerQueryFormulas[3];
Console.WriteLine(powerQueryFormula.Name); // expected: FIlls
Console.WriteLine(powerQueryFormula.Group); // not possible, expected: Fills / test
powerQueryFormula = workbook.DataMashup.PowerQueryFormulas[4];
Console.WriteLine(powerQueryFormula.Name); // expected: fill_url
Console.WriteLine(powerQueryFormula.Group); // not possible, expected: Fills / test
powerQueryFormula = workbook.DataMashup.PowerQueryFormulas[5];
Console.WriteLine(powerQueryFormula.Name); // expected: Table 0
Console.WriteLine(powerQueryFormula.Group); // not possible, expected: Other Queries
powerQueryFormula = workbook.DataMashup.PowerQueryFormulas[6];
Console.WriteLine(powerQueryFormula.Name); // expected: from_timestamp
Console.WriteLine(powerQueryFormula.Group); // not possible, expected: Other Queries
powerQueryFormula = workbook.DataMashup.PowerQueryFormulas[7];
Console.WriteLine(powerQueryFormula.Name); // expected: Invoked Function
Console.WriteLine(powerQueryFormula.Group); // not possible, expected: Other Queries
Can you update the PowerQueryFormulas collection to (1) return the queries in the same order as in Excel, and (2) add a property (or some structure) to relate the group of a query?
We’re using Aspose.Cells 24.11.0 for .NET. Thank you!
Thanks for the template Excel file, screenshot and details.
We evaluated your requirements/details. We will check if we could accommodate the order of queries returned by Aspose.Cells to be same with MS Excel. We will also check if we could support to read the group of a query and add group to PowerQueryFormulas.
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-57253
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.
@perfectxl
We will try to add PowerQueryFormula.GroupName property.
If the formulas is not grouped , it will be in “Other Queries”, but if there is no grouped formulas, Excel does not show “Other Queries” . So PowerQueryFormula.GroupName will return null if it’s not grouped. Does it fit your need?
But we can not order them because we can not find the solution now. We found the order will be same as MS Excel if we reversed grouped Formulas and ungrouped formulas in PowerQueryFormulaCollection. So please get grouped formulas first by yourself now.
Yes, the proposal of the PowerQueryFormula.GroupName property would fit our need, thank you.
It would be helpful if Aspose.Cells can already apply the suggested order (reversed grouped formulas and ungrouped formulas). If you found that this is the correct ordering, can you apply that for the collection of workbook.DataMashup.PowerQueryFormulas?
Note that you can reorder groups and reorder queries by drag-and-drop in the left sidebar of the Power Query Editor in Excel. So please verify if the order is indeed correct after creating and reordering some groups and queries, it does not have to be alphabetically.
Thank you for investigating and providing better support for Power Query!
This is to inform you that your issue has been resolved as per the description we provided in the post. The enhancement/fix will be included in the upcoming release (Aspose.Cells for Java v24.12) that we plan to release in the first half of December 2024. You will be notified when the next version is released.
The issues you have found earlier (filed as CELLSNET-57253) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi