Free Support Forum - aspose.com

Feature missing - update of PowerQueryItems Value and Name

Hi Aspose team,

attached project you can find code to read PowerQueryFormulaItem value and name. As those are only getter I would like to ask you to extend them to be setter also as I’ll need to change content of those 2 fields.

Btw. it is more important to get setter for value field but both would be perfect.

P.S. I believe that same is already described in bug:
CELLSNET-46924 – Access to link paths contained in DataMashups

Thanks,
Oliver

Mash.zip (113.4 KB)

@dr.doc,
Thank you for providing more details.
We have understood the requirements but we need to look into it more. We have logged the issue in our database for investigation and for a fix(if possible). Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-47091 – Option to update PowerQueryFormulaItems

Hi,

thanks for opening ticket. Can you please check with developers how long it will need to get this functionality? If needed to accelerate I can create paid support ticket.

Thanks,
Oliver

@dr.doc,
As we just logged the ticket, so could you spare us a little time (3-5 days or so) for complete evaluation and investigation for your issue. If it is not much complex, you should be receiving the fix before the end of next week or even before it. If the issue is complex, it might take a couple of weeks. We will let you know if paid support can accelerate the fix.

@dr.doc,
We will support this feature in Q1 2020. It’s a complex feature, so we need more time to support it.

Hi Aspose team,

just to check - can we still count to get this feature in Q1/2020?

Thx,
Oliver

@dr.doc,

Yes, hopefully the feature will be supported before the end of Q1/2020. We will start working on it in the next week.

@dr.doc,
Could you explain more about updating PowerQueryFormula?
Which parts do you want to update?
We find it’s hard to update attached Metadata XML because there is relationship between this Metadata and querytable.

Hi,

this is example where I am reading PowerQueryFormulaItems:

public void UpdateMash()
{

        Workbook workbook = new Workbook(@"Mash\master.xlsx");
        Aspose.Cells.QueryTables.DataMashup mashupData = workbook.DataMashup;
        foreach (Aspose.Cells.QueryTables.PowerQueryFormula f in mashupData.PowerQueryFormulas)
        {
            Console.WriteLine(f.Name);
            foreach (Aspose.Cells.QueryTables.PowerQueryFormulaItem item in f.PowerQueryFormulaItems)
            {
                Console.Write(item.Name + " = ");
                Console.WriteLine(item.Value);
            }
        }
    }

I’ll need to be able to update item.Value.

For example, if you run attached project you will see output like:

Source = Excel.Workbook(File.Contents(“C:_Work\Kai\Source\Source.xlsx”), null, true)
Sheet1_Sheet = Source{[Item=“Sheet1”,Kind=“Sheet”]}[Data]
Changed Type = Table.TransformColumnTypes(Sheet1_Sheet,{{“Column1”, Int64.Type}, {“Column2”, Int64.Type}, {“Column3”, Int64.Type}})

At the end, it is about getting option to change value of Source field as when we move documents from one location to the other none of PowerQueryFormulas will work due to this change and we have to update value here to be able to point to new document location.

Thx

@dr.doc,
Thank you for providing more information. We will analyze it and provide our feedback accordingly.

@dr.doc,

Please share if you want to update source field only, and the data of the source file is same?

According to what we saw in Excel file I would say that we will need to update only Source field as it contains path to source file but might happen that in other fields there might be also some references to source files.

Let us say that you can start with Source field and if we notice, later, that we might need something else we will let you know. Would that work for you?

@dr.doc,

Thanks for sharing your requirements.

We will look into it soon.

@dr.doc,
Please try our latest version/fix: Aspose.Cells for .NET v20.2.2 (attached)

Your issue should be fixed in it.

Let us know your feedback.

Aspose.Cells20.2.2 For .Net2_AuthenticodeSigned.Zip (5.2 MB)
Aspose.Cells20.2.2 For .Net4.0.Zip (5.2 MB)

Hi,

thanks for this. Just confirm that I am able now to change PowerQueryFormulaItem.

Thanks,
Oliver

@dr.doc,
Yes, you will be able to update source field of PowerQueryFormulaItems with this version.