Run Power Query refresh within Aspose? [.net]

Aspose.Cells Product Family
1

Hello,

My apologies if it’s something I just couldn’t find in the API documention -

I have a workbook using power query. There are queries to sheets -within the same workbook- (not external data). I’m using smart tags to populate a data tab, I then need to refresh the queries referencing that data tab.

How do I run a “Refresh All” of the data within .net?

2

@User65432,

Could you please zip and share your template Excel file containing power query table(s) referencing to same workbook. We will check it soon.

3

Template.zip (107.2 KB)

Please see the attached template.

I’m trying to do the equivalent of Data Tab -> Refresh All
All queries are in Data->Queries & Connections-> Queries
All queries reference sheets within the same workbook - no external data sources

The full workflow I’m trying to do if you have suggestions:

  • Populate Excel file with data
    • Completed by using smart tags
  • Refresh power query sources to build the tables referencing the new data
    • Unsure how to do
  • Delete power query sources after tables have been updated to remove the security warning for clients
    • Unsure how to do
4

@User65432,

Thanks for the template file.

I found the data connections involve OLEDB queries in your provided file. I am afraid, currently, Aspose.Cells does not support to refresh data for PivotTable or Table with DataModel. So, Power Query refresh and Power Pivot tables refresh might not work. Still we will discuss it with the team and get back to you soon.

5

Is there a way to use power query/power pivot in a way that is supported by Aspose?

I need a way for Excel to be able to transform the data after Aspose populates the data sheet. I then need that data to be loaded into other sheets

Transforming the data in c# is not possible as this tool is used to dynamically build reports so a custom workflow will not be supported.

6

@User65432,

Please spare us little time as we will evaluate and check it. We will get back to you soon.

7

@User65432,

I am afraid, currently, Aspose.Cells does not support to refresh or manipulate query tables with data model. I have logged a ticket with an id “CELLSNET-49449” for your requested feature. We will look into it. Please note, since the feature is complex, so it might take long time to support it.

Once we have an update on it, we will let you know.

8

Any updates / ETA on this one? (crossing my fingers - would be a life saver :wink: )
tia

9

@tokebreer2,
Due to the problem of document protocol, we can’t support it for the time being. Sorry for the inconvenience.