Free Support Forum - aspose.com

Power Query Parameters and Refresh Pivot Table

Good afternoon,

I am having trouble updating parameters within the DataMashup > PowerQueryFormula. Currently any PowerQueryFormulaItem > Value that I update, I get an error when I try to save the workbook:

System.NullReferenceException: ‘Object reference not set to an instance of an object.’

**Please note that I am working with Web.Contents by getting data via an API call and this error happens when I call workbook.Save. If I open the workbook and Save without modifying the PowerQueryFormulaItem > Value, it works fine.

Separately, I am also getting an error when trying to RefreshData on the Pivot Table, and am getting the error:

Aspose.Cells.CellsException: ‘this data source is not supported.’

(I am using the Power Pivot Add In, which also might be causing an issue)

If you can let me know if there are current limitations as I know this is a new feature for Aspose. I am more than willing to work with you to test in order to figure out a way to automatically refresh API calls within Power Query, while updating the parameters. This is a super powerful feature that would give Aspose quite the edge to be able to support this. It would make our old reporting methods basically obsolete, where we wouldn’t have to configure everything in C# .Net code!

https://docs.aspose.com/display/cellsnet/Update+Power+Query+Formula+Item

Thanks,
Michael Kelly

@mkellyOHA,
Please share your template Excel file, runnable console application, program output and expected output created by MS Excel along with images showing the issue. We will analyze the information and provide assistance accordingly.

Please see the attached files:
TestPowerRefreshExample.zip (5.7 MB)

TestPowerRefresh:
Please see the below images which indicate that the Pivot tables cannot be refreshed in line 31:
image.png (8.6 KB)

If line 31 is commented out, the workbook.Save works in code, but it doesn’t update ParameterSource like you would expect it to.

TestPowerRefresh2:
Also, see the below attached image of workbook.Save failing when lines 24-26 are uncommented and lines 20-22 are commented. The key to this is that when the parameter is created by default without a “ParameterSource =” line.
image.png (12.7 KB)

This, in turn produces a corrupted output file:
image.png (4.6 KB)

Let me know if you have any questions. I would really like to get this working if at all possible. It would be really incredible to do something like this. The postman url doesn’t need any authentication, so you should be good there.

Thanks,
Michael kelly

@mkellyOHA,
We have observed both the issues and have logged them as follows. We will update you here once any feedback is ready to share.

CELLSNET-47434 - "This data source is not supported" exception while calling RefreshData on the Pivot Table

CELLSNET-47435 - Error while updating parameter in DataMashup > PowerQueryFormula

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

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

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

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

Thanks,
Michael Kelly

@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.

@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.

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

@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.

@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.

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

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

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

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

@mkellyOHA,

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

Amjad,

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

Thanks,
Michael Kelly

@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.