Power Query Parameters and Refresh Pivot Table

Aspose.Cells Product Family
5

Do you happen to know if Power Query refresh AND Power Pivot tables refresh should both be able to work with data from all sources in Aspose.Cells?

The programmatic refresh would be highly advantageous, as I only know of Excel Interop that currently supports this capability, and nothing using .Net Core.

I’m happy to discuss and test.

Thanks,
Michael Kelly

6

@mkellyOHA,
We have noted your query and will discuss it soon. You will be notified here once any feedback is ready for sharing.

7

@mkellyOHA,
We can’t support PivotTable with DataModel for now, we will support it later.

8

Do you know a timeline? Will this be after .Net 5 comes out? Any specifics will help us plan accordingly.

Thanks,
Michael Kelly

9

@mkellyOHA,
There is no plan to provide this feature in near future. We will write back here once any update regarding its resolution is ready to share.

10

@mkellyOHA,
Please try our latest version/fix: Aspose.Cells for .NET v20.6.2:

Aspose.Cells20.6.2 For .Net4.0.Zip (5.4 MB)
Aspose.Cells20.6.2 For .Net2_AuthenticodeSigned.Zip (5.4 MB)

Your issue CELLSNET-47435 should be fixed in it.

Let us know your feedback.

11

I will test it when I get the chance. But unless I can use the Power Pivot Model to be refreshed from the Power Query and the updated parameters, then I have no use for trying to automate this right now. Please let me know when you have a timeline prepared for this. I’m not kidding when I say it will revolutionize reporting. It would be second only to Power BI, but with the ability to run locally and have paginated reports (only a Power BI Premium feature at the moment).

Please talk to your supervisors and tell them this would be a huge selling point for their product. Virtually low to no code needed. People who only know Excel formulas could do this. If you need to setup a meeting for me to explain this to them, I’d be happy to do so.

Best regards,
Michael Kelly

12

@mkellyOHA,

Thanks for your comments and concerns.

Well, this is complex feature and might not be supported in quick time. Anyways, we will discuss with the team to check the feasibility. We will get back to you soon.

13

@mkellyOHA,

We discussed and analyzed your desired feature with the team. We have decided to put good effort to implement the feature. Hopefully, we could support PivotTable with DataModel in the third quarter of 2020. So the rough ETA for the feature is q3, 2020.

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

14

Thank you Amjad, once this update is deployed I will spread the word to several contacts who would appreciate this very much! This work will be very beneficial. Let me know if I can test anything for you, I’d like to help anyway I can for this.

Thanks,
Michael

15

@mkellyOHA,
Sure we will let you know once any update is ready to share.

16

Hey, I just tested out the CELLSNET-47435 issue and it seems that while the ParameterSource PowerQueryFormulaItem can now be updated in code, and the workbook be saved without error, when I open up the file the ParameterSource remains unchanged from the original file.

ALSO, it does not refresh the data from the PowerQuery source either. I would assume it should be able to refresh the data from PowerQuery source as well as update the parameter? Otherwise, I am unsure why being able to update the PowerQueryFormula or PowerQueryFormulaItem without it affecting the file is beneficial at all…

THIS IS ONLY FOR LOADING POWER QUERY DATA STRAIGHT TO EXCEL WORKSHEET. NO POWER PIVOT OR PIVOT TABLES INVOLVED.

Again, let me know if I am doing something wrong, or if this Bug needs to be reopened? I will be able to test these changes whenever you have them complete.

It may have to do with this request, as I have the same issue:

Regards,
Michael Kelly

17

@mkellyOHA,
We have noted your feedback and will share our comments after detailed analysis.

18

@mkellyOHA,

Please note, all Power Query Parameters are added into DataMashup.PowerQueryFormulaParameters.

19

Amjad,

Please note, I do not see any DataMashup.PowerQueryFormulaParameters class. Has this been implemented yet?
image.png (5.9 KB)

Thanks,
Michael Kelly

20

@mkellyOHA,

Please try using our latest fix, i.e., Aspose.Cells for .NET v20.6.2 (the links are already provided in the post in the thread), the attribute is there under DataMashup.

21

The issues you have found earlier (filed as CELLSNET-47435) have been fixed in Aspose.Cells for .NET v20.7. This message was posted using Bugs notification tool by ahsaniqbalsidiqui

22

In your next release, could you please change Parameter Items to be editable? It looks like they are ready-only currently. This would make it easier to separate Parameters from Formula items.
image.png (5.5 KB)

Ease of use for developers will go a long way when using Power Query/Power Pivot in .NET.

Also, I am having trouble generating a refreshed version of the report with the new parameter. What is the correct way to Refresh All? Or is this not supported?

23

@mkellyOHA,

We have created a separate ticket for this as follows:
CELLSNET-47489-Provision to edit PowerQueryFormulaParameters

Could you please create a new thread along with the runnable sample code and template files for our reference? Provide complete details with images and steps so that we may reproduce the issue here and provide feedback accordingly.

24

Thank you, and here is the link to the new item: