Showing results for 
Search instead for 
Did you mean: 
Not applicable

Power BI Report Server dataset API presents data as JSON - Power Pivot expects XML - how do I fix?

We're working with an on-premises Power BI Report Server and I have a cached dataset.  I want to make this available to Power Pivot users but it is expecting an XML structure.  When I enter the URL for the data feed (http://<reportserver>/Reports/api/v2.0/datasets(<ID>)/data), I get this error: Cannot connect to the specified feed.  Verify the connection and try again.  Reason: Data at the root level is invalid.  Line 1, position 1.


When I put the URL into a browser, I get the data in a JSON format.  When I use Power Query, I can see the data.  When I use Power BI Desktop, I can see the data.  Can someone help explain what I need to do in order to get this data to be made available for Power Pivot?  I'm using Excel 2016 in case that's important.


Helpful resources



We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!


The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.