Odata v4 & Power Pivot

Question

Odata v4 & Power Pivot

Jon Kvalø 1

Hi. Our new ERP system are using Odata v4 for the odata feeds, and I am not any longer able to connect Power Pivot to these feeds. Do anyone know wheteher Power Pivot supports Odata v4 data or not?

Emily Hua-MSFT 27,796 Reputation points

2021-02-19T08:53:10.393+00:00

@Jon Kvalø
Is there any update?
If your issue was resolved, welcome post back and share us your solution.
Jon Kvalø 1 Reputation point

2021-02-24T10:36:27.947+00:00

The matter is not resolved. I have not found out how to load odatav4 feeds from Microsoft Dynamics BC365 into PowerPivot. (I know it can be done with PowerQuery)
Lz._ 9,016 Reputation points

2021-02-24T14:11:06.987+00:00

Thanks @Jon Kvalø re. it can be done with PowerQuery
Jeffrey Patch 0 Reputation points

2023-10-11T02:02:25.5766667+00:00

This function stopped working a few years ago and Microsoft have not bothered to fix it or advise how to work around it.

2 answers

Your answer

Emily Hua-MSFT 27,796 Reputation points

2021-02-19T08:53:10.393+00:00

@Jon Kvalø
Is there any update?
If your issue was resolved, welcome post back and share us your solution.
Jon Kvalø 1 Reputation point

2021-02-24T10:36:27.947+00:00

The matter is not resolved. I have not found out how to load odatav4 feeds from Microsoft Dynamics BC365 into PowerPivot. (I know it can be done with PowerQuery)
Lz._ 9,016 Reputation points

2021-02-24T14:11:06.987+00:00

Thanks @Jon Kvalø re. it can be done with PowerQuery
Jeffrey Patch 0 Reputation points

2023-10-11T02:02:25.5766667+00:00

This function stopped working a few years ago and Microsoft have not bothered to fix it or advise how to work around it.

Answer 1

Lz._ 9,016

Hi @Jon Kvalø

From your description it seems you've been importing oData data into the Data Model straight from Power Pivot (if this isn't the case, please clarify):

TBH I have no idea which oData versions are supported by Power Pivot. However - AFAIK - the oData connector for Power Query/Get & Transform and Power BI supports oData v4

So, instead of connecting to your oData feeds from Power Pivot, proceed as follow (summary of steps) - from Excel:

Go to the Data tab > Get Data > From Other Sources > From oData Feed
Enter your url > OK
Select your table(s)
At the bottom of the Navigator window > Click on the arrow down next to Load > Load To... > Check options Only Create Connection + Add this data to the Data Model

Lz._ 9,016 Reputation points

2021-02-16T11:48:50.04+00:00

Hi @Jon Kvalø . Have you had time to check the above possible solution with your ERP? It would be appreciated to get your feedback as other users/partners could benefit from your experience. Thanks in advance
Jon Kvalø 1 Reputation point

2021-02-24T10:37:53.583+00:00

Hi. This was really not the issue. My issue wer that I can notsee how I can import a Odatav4 stream coming from the ERP system to PowerPivot. It doesnt look like PowerPivot support data with Odatav4 protocol.
Jeffrey Patch 0 Reputation points

2023-10-11T02:04:27.1266667+00:00

Its all well and good to advise to use Power Query connection, but there are a number of disadvantages - e.g. cannot change timeout settings, have to change all existing spreadsheets that used the power pivot approach. Microsoft should just fix it or deprecate it it rather than leaving it around.

Answer 2

Emily Hua-MSFT 27,796

@Jon Kvalø
Please refer to Lz-3068's answer to have a check.
Besides, you may check whether this article "ODATA Reads" is helpful.

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Lz._ 9,016 Reputation points

2021-02-16T11:41:04.227+00:00

OData Feed through Excel or Power Pivot vs. Power Query article is old. However, after checking my Microsoft 365 installation the following still appear to be true:
Power Pivot uses Microsoft.Data.DataFeedClient (%ProgramFiles%\Microsoft Office\root\vfs\ProgramFilesCommonX64\Microsoft Shared\OFFICE16\DataModel)
Power Query uses Microsoft.Data.OData (%ProgramFiles%\Microsoft Office\root\Office16\ADDINS\Microsoft Power Query for Excel Integrated\bin)

Couldn't find info. re. the oData version(s) suppored by the former though
Emily Hua-MSFT 27,796 Reputation points

2021-02-19T08:52:05.323+00:00

@Lz._
Thanks for your kind sharing :)
Jon Kvalø 1 Reputation point

2021-02-24T10:38:33.06+00:00

See my comments above. The issue is not solved.
Emily Hua-MSFT 27,796 Reputation points

2021-03-01T09:55:04.367+00:00

@Jon Kvalø
As I do not have a test environment for further research, to better help you, I suggest you post a new thread on Microsoft Community forum.
Thanks for your understanding.

Share via

Odata v4 & Power Pivot

2 answers

Your answer