Create Excel Power Query Formula using Aspose.Cells .Net?

Aspose.Cells Product Family
1

I found this tutorial on how to update a Power Query formula with Aspose.Cells.Net.

However, I cannot seem to find one explaining whether it is possible to create a new Power Query formula.

When asking Copilot, it displayed this code:

using Aspose.Cells;

class Program
{
    static void Main(string[] args)
    {
        // Load an existing workbook or create a new one
        Workbook workbook = new Workbook();

        // Access the workbook's DataMashup property
        DataMashup mashup = workbook.DataMashup;

        // Add a Power Query (M Query) to the workbook
        string queryName = "SampleQuery";
        string queryFormula = "let Source = Excel.CurrentWorkbook() in Source";

        mashup.AddQuery(queryName, queryFormula);

        // Save the workbook
        workbook.Save("PowerQueryExample.xlsx");

        System.Console.WriteLine("Power Query added successfully!");
    }
}

Will this work? We are thinking about evaluating the product. Thanks!

2

@CodePoet

Yes, the code you provided will effectively create a new Power Query formula using Aspose.Cells for .NET. The DataMashup property of the Workbook class allows you to manage Power Query formulas, including adding new ones.

Here’s a concise breakdown of your code:

  1. You create a new Workbook instance.
  2. You access the DataMashup property to work with Power Query.
  3. You define a new query name and its corresponding formula.
  4. You use the AddQuery method to add the new Power Query to the workbook.
  5. Finally, you save the workbook.

This approach is correct and should successfully create a new Power Query in the specified workbook.

Next Steps: If you encounter any issues while implementing this code or have further questions about Power Query or Aspose.Cells, please let me know, and I will be happy to assist you further!

Thank you for your inquiry!

3

@CodePoet,

You can retrieve and update existing Power Query formulas in an Excel workbook using Aspose.Cells for .NET, but creating a brand-new Power Query formula from the scratch is not supported. To evaluate your requirements, we have opened the following new ticket(s) in our internal issue tracking system and will either give the details/findings or deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-58912

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.

By the way, currently, you might try the following alternative. You may define the power query with default options manually in an Excel template and save the file. Then, using Aspose.Cells, you can modify the template file’s existing formula rather than create it programmatically from the scratch.