Update Power Query Formula Item

Usage Scenario

There might be cases where the data source files are moved and the excel file is unable to locate the file. In such cases, Aspose.Cells API provides the option to update the Power Query Formula item by using the PowerQueryFormulaItem class to update the location of the source file.

Updating Power Query Formula Item

Aspose.Cells API provides the ability to update Power Query Formula Items. The following code snippet demonstrates updating the data source file location in the excel file by using the PowerQueryFormulaItem.Value property. The source and output files attached for your reference.

• Source File 1
• Source File 2
• Output File

Sample Code

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// Working directories
string SourceDir = RunExamples.Get_SourceDirectory();
string outputDir = RunExamples.Get_OutputDirectory();

Workbook workbook = new Workbook(SourceDir + "SamplePowerQueryFormula.xlsx");
DataMashup mashupData = workbook.DataMashup;
foreach (PowerQueryFormula formula in mashupData.PowerQueryFormulas)
{
    foreach (PowerQueryFormulaItem item in formula.PowerQueryFormulaItems)
    {
        if (item.Name == "Source")
        {
            item.Value = "Excel.Workbook(File.Contents(\"" + SourceDir + "SamplePowerQueryFormulaSource.xlsx" + "\"), null, true)";
        }
    }
}

// Save the output workbook.
workbook.Save(outputDir + "SamplePowerQueryFormula_out.xlsx");