{"id":3797,"date":"2021-11-29T00:44:35","date_gmt":"2021-11-29T08:44:35","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3797"},"modified":"2021-11-29T00:44:37","modified_gmt":"2021-11-29T08:44:37","slug":"how-to-load-data-from-an-odata-feed-into-power-query-desktop-and-online","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3797","title":{"rendered":"How to Load Data from an OData Feed into Power Query Desktop and Online"},"content":{"rendered":"\n

<\/a>Summary<\/h2>\n\n\n\n
Item<\/th>Description<\/th><\/tr><\/thead>
Release State<\/td>General Availability<\/td><\/tr>
Products<\/td>Power BI (Datasets)
Power BI (Dataflows)
Power Apps (Dataflows)
Excel
Dynamics 365 Customer Insights
Analysis Services<\/td><\/tr>
Authentication Types Supported<\/td>Anonymous
Windows
Basic (requires Gateway)
Web API
Organizational Account<\/td><\/tr>
Function Reference Documentation<\/td>OData.Feed<\/a>, ODataOmitValues.Nulls<\/a><\/td><\/tr>
<\/td><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

 Note<\/p>\n\n\n\n

Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities.<\/p>\n\n\n\n

<\/a>Load data from an OData Feed in Power Query Desktop<\/h2>\n\n\n\n

To load data from an OData Feed in Power Query Desktop:<\/p>\n\n\n\n

  1. Select the OData<\/strong> or OData Feed<\/strong> option in the connector selection.<\/li>
  2. Choose the Basic<\/strong> button and enter a URL address in the text box. This URL should be the root of the OData service you want to connect to. For example, enter http:\/\/services.odata.org\/V4\/northwind\/northwind.svc\/<\/code>. Then select OK<\/strong>.\"ODataIf the URL address you enter is invalid, a \"Warning warning icon will appear next to the URL<\/strong> textbox.<\/li>
  3. If this is the first time you’re connecting using the OData Feed, select the authentication type, input your credentials (if necessary), and select the level to apply the authentication settings to. Then select Connect<\/strong>.\"OData<\/li>
  4. From the Navigator<\/strong> dialog, you can select a table, then either transform the data in the Power Query Editor by selecting Transform Data<\/strong>, or load the data by selecting Load<\/strong>.\"WebIf you have multiple tables that have a direct relationship to one or more of the already selected tables, you can select the Select Related Tables<\/strong> button. When you do, all tables that have a direct relationship to one or more of the already selected tables will be imported as well.<\/li><\/ol>\n\n\n\n

    <\/a>Load data from an OData Feed in Power Query Online<\/h2>\n\n\n\n

    To load data from an OData Feed in Power Query Online:<\/p>\n\n\n\n

    1. Select the OData<\/strong> or OData Feed<\/strong> option in the connector selection.<\/li>
    2. In the OData dialog that appears, enter a URL in the text box.\"OData<\/li>
    3. If this is the first time you’re connecting using the OData Feed, select the authentication kind and enter your credentials (if necessary). Then select Next<\/strong>.<\/li>
    4. From the Navigator<\/strong> dialog, you can select a table, then transform the data in the Power Query Editor by selecting Transform Data<\/strong>.\"WebIf you have multiple tables that have a direct relationship to one or more of the already selected tables, you can select the Select Related Tables<\/strong> button. When you do, all tables that have a direct relationship to one or more of the already selected tables will be imported as well.<\/li><\/ol>\n\n\n\n

      <\/a>Known Issues and Limitations<\/h2>\n\n\n\n

      <\/a>Joins<\/h3>\n\n\n\n

      Due to the architecture of OData and other web connectors, joins can be non-performant. While you have the option to use navigation columns when merging between tables from an OData source, you don’t have this option when merging with non-Odata sources.<\/p>\n\n\n\n

      If you are seeing performance issues when merging an OData source, you should apply Table.Buffer<\/a> to your OData query in the Advanced Editor, before you merge the data.<\/p>\n\n\n\n

      <\/a>Test Connection issues<\/h3>\n\n\n\n

      In cases where you’re passing in a URL to the OData connector that’s not just the service root, for example, if you have a filter on the URL, when you set up refresh in the service you should select Skip Test Connection<\/strong>.<\/p>\n\n\n\n

      <\/a>Authenticating to arbitrary services<\/h3>\n\n\n\n

      Some services support the ability for the OData connector to authenticate with OAuth\/AAD authentication out of the box. However, this won’t work in most cases.<\/p>\n\n\n\n

      When attempting to authenticate, if you see the following error:<\/p>\n\n\n\n

      \u201cWe were unable to connect because this credential type isn\u2019t supported for this resource. Please choose another credential type.\u201d<\/p>\n\n\n\n

      \"Error<\/figure>\n\n\n\n

      Contact the service owner. They’ll either need to change the authentication configuration or build a custom connector.<\/p>\n\n\n\n

      <\/a>Maximum URL length<\/h3>\n\n\n\n

      If you’re using the OData feed connector to connect to a SharePoint list, SharePoint online list, or Project Online, the maximum URL length for these connections is approximately 2100 characters. Exceeding the character limit results in an 401 error. This maximum URL length is built in the SharePoint front end and can’t be changed.<\/p>\n\n\n\n

      To get around this limitation, start with the root OData endpoint and then navigate and filter inside Power Query. Power Query filters this URL locally when the URL is too long for SharePoint to handle. For example, start with Copy<\/p>\n\n\n\n

      OData.Feed(\"https:\/\/contoso.sharepoint.com\/teams\/sales\/_api\/ProjectData\")\n<\/code><\/pre>\n\n\n\n
      instead, copy<\/p>\n\n\n\n
      OData.Feed(\"https:\/\/contoso.sharepoint.com\/teams\/sales\/_api\/ProjectData\/Projects?<\/code><\/pre>\n\n\n\n
      Ref: https:\/\/docs.microsoft.com\/en-us\/power-query\/connectors\/odatafeed<\/p>\n","protected":false},"excerpt":{"rendered":"
      Summary Item Description Release State General Availability Products Power BI (Datasets)Power BI (Dataflows)Power Apps (Dataflows)ExcelDynamics 365 Customer InsightsAnalysis Services Authentication Types Supported AnonymousWindowsBasic (requires Gateway)Web APIOrganizational Account Function Reference Documentation OData.Feed, ODataOmitValues.Nulls  Note Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities. Load data from an OData Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[238,239,10,621],"tags":[960],"class_list":["post-3797","post","type-post","status-publish","format-standard","hentry","category-cloud","category-azure","category-microsoft","category-power-bi","tag-load-data-from-an-odata-feed-into-power-query"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3797","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3797"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3797\/revisions"}],"predecessor-version":[{"id":3799,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3797\/revisions\/3799"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3797"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3797"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3797"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}