Q:

Excel data pull from External Source odata feed does not work

We have got a ODATA feed web API, which is a data source for our Excel report.  This Odata feed, we are able to pull successfully, when it is hosted local & physical server.  When the same odata feed web API is hosted in cloud environment we are facing connection timeout issue.  

Unable to Connect
  We encountered an error when trying to connect
  Details: "OData: Request failed: Unable to connect to the remote
  server"

The same odata feed in cloud is accessible when we use office data connection(odc) in the excel file.  
<html xmlns:o="urn:schemas-microsoft-com:office:office"
                            xmlns="http://www.w3.org/TR/REC-html40">

                            <head>
                            <meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
                            <meta name=ProgId content=ODC.TableCollection>
                            <meta name=SourceType content=DATAFEED>
                            <title>OurApplication</title>
                            <xml id=docprops><o:DocumentProperties
                              xmlns:o="urn:schemas-microsoft-com:office:office"
                              xmlns="http://www.w3.org/TR/REC-html40">
                              <o:Name>OurApplication</o:Name>
                             </o:DocumentProperties>
                            </xml><xml id=msodc><odc:OfficeDataConnection
                              xmlns:odc="urn:schemas-microsoft-com:office:odc"
                              xmlns="http://www.w3.org/TR/REC-html40">
                              <odc:Connection odc:Type="DATAFEED">
                               <odc:ConnectionString>
Data Source=&quot;https://OurApplication.com/odata/OurTable&quot;
</odc:ConnectionString>
                               <odc:CommandType>TableCollection</odc:CommandType>
                               <odc:CommandText>&quot;OurTable&quot;</odc:CommandText>
                              </odc:Connection>
                             </odc:OfficeDataConnection>
                            </xml>
                            <style>
                            </style> 
                            </head> 
                            </html>  

But, it is not accessible when we try to do bring using Get External Data as given below:

We are getting below error in fiddler.  

[Fiddler] The connection to 'OurApplication.com' failed.  Error:
  TimedOut (0x274c).  System. Net. Sockets. SocketException A
  connection attempt failed because the connected party did not properly
  respond after a period of time, or established connection failed
  because connected host has failed to respond
  OurApplicationIPAddress:OurApplicationPortNumber

When I try to use M language for OData source, I am getting error like below:
= OData. Feed ("OurApplication/odata/OurTable"
    ,null, [Timeout = #duration(0,2,0,0)])

DataSource. Error: OData: Request failed: The remote server returned an
  error: (502) Bad Gateway. ( Fiddler - Connection Failed) Details:
      DataSourceKind=OData
      DataSourcePath=http://Ourapplication:portnumber/odata/$metadata
      Url=http://courapplication/odata/$metadata

Please help us in resolving how to connect to ODATA feed in Powerquery in excel.                                                                                                                                                

A:

In our case, basic authentication was not working for the PaaS (we were using cloud foundary based PaaS hosting) hosted webAPI.   We created one more endpoint with anonymous authentication and used that endpoint in our excel connection to get data using OData.   
Note: In our case, as the data was not sensitive and moreover, being internal application, we went ahead with anonymous authentication. 