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