none
Issue consuming Odata feed with Power Query (not in Power BI)

    Question

  • I am trying to consume an OData feed from an SSRS report using Power Query ( latest release on Excel  2010 x32). The Report server is in my company intranet.

    In the SSRS report, I can generate an Atom service file. When I use this file in PowerPivot, I can successfully import the report data and refresh it on demand. But I would like to do the same thing in Power Query using the "From OData feed" feature.

    1. I have tried supplying the URI to the service file

    = OData.Feed("file:///C:/Users/Bdarbonneau/Documents/temp/Manuf_cycle_time_mapping_table.atomsvc")

    I get this error : 

    DataFormat.Error: The supplied URL must be a valid 'http:' or 'https:' URL.
    

    2. I tried supplying the URL that the service file contains, but without success. 

    = OData.Feed("http://myssrsserver:8080/ReportServer?%2FMANUFACTURING%2FArchive%2FManuf_cycle_time_mapping_table&rs%3ACommand=Render&rs%3AFormat=ATOM&rc%3AItemPath=Tablix1")

    I get this error:

    DataFormat.Error: OData: The given URL neither points to an OData service or a feed

    Am I missing something, or is what I am trying to do not supported ? 

    Regards,

    Bertrand

    Friday, March 28, 2014 10:34 AM

Answers

  • My Current workaround for pulling data from SSRS until the dev team have worked out the odata issue is to pull the report in as a csv file:

    Csv.Document(Web.Contents("http://Servername/ReportServer?/SummaryReport&rs:Command=Render&rs:Format=Csv")),

    I also tried pulling an excel file from SSRS with no success.

    Tried:

    Excel.Workbook(URL)

    Excel.Workbook(Web.Contents(URL))

    Excel.Workbook(File.Contents(URL))

    Excel.Workbook(File.Contents(Web.Contents(URL)))

    if anyone has had luck pulling in an excel file from SSRS i would like to know how.

    Is there a rough release date for the odata functionality?

    Sunday, November 02, 2014 8:37 PM

All replies

  • The OData support in Power Query does not currently work with the Atom feed generated by SSRS. This is something we're hoping to change in the near future.
    Friday, March 28, 2014 1:36 PM
    Moderator
  • Has the near future cometh yet?

    We have a use case to use an SSRS invoice-lookup report, with Power Query.  It would be nice to use these ATOMSVC file that SSRS provides.

    Sunday, June 01, 2014 1:45 PM
  • July 1. The change is yet to come.
    Tuesday, July 01, 2014 11:37 AM
  • Sep 15 and a few PQ releases later: Still unsupported. What should I do to ensure that this requirement is directed to the developement team ? 


    • Edited by Bda75 Monday, September 15, 2014 9:33 AM
    Monday, September 15, 2014 9:33 AM
  • I'm on the development team :P and quite aware of this request.
    Tuesday, September 16, 2014 3:30 PM
    Moderator
  • My Current workaround for pulling data from SSRS until the dev team have worked out the odata issue is to pull the report in as a csv file:

    Csv.Document(Web.Contents("http://Servername/ReportServer?/SummaryReport&rs:Command=Render&rs:Format=Csv")),

    I also tried pulling an excel file from SSRS with no success.

    Tried:

    Excel.Workbook(URL)

    Excel.Workbook(Web.Contents(URL))

    Excel.Workbook(File.Contents(URL))

    Excel.Workbook(File.Contents(Web.Contents(URL)))

    if anyone has had luck pulling in an excel file from SSRS i would like to know how.

    Is there a rough release date for the odata functionality?

    Sunday, November 02, 2014 8:37 PM
  • I have seen in other threads that v2.22.4007.242 has new features related to using Atom feeds. Does it imply that consuming SSRS reports in PQ is now possible ?
    Tuesday, May 12, 2015 8:08 AM
  • I sure hope so. I'm trying to reconcile data between SCCM and our Service Now CMDB. Power Query could make my life so much better.

    Friday, May 15, 2015 3:25 PM
  • Nothing has changed with respect to consuming Atom feeds from Power Query.
    Friday, May 15, 2015 3:44 PM
    Moderator