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

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.
    • Proposed as answer by Curt HagenlocherModerator Friday, March 28, 2014 1:37 PM
    • Marked as answer by Bda75 Friday, March 28, 2014 4:00 PM
    • Unmarked as answer by Bda75 Tuesday, May 12, 2015 3:46 PM
    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