Issue while using a shared dataset via ODATA in Power BI Report Server/Desktop

[TePe]

So we created a shared data source in Report Server based on AdventureworksDW2016 (the dimCustomer table). We want to use it as a data source for Power BI. So I tried first to find out the GUID of the data source (any easy way how to do that? I connected to the "catalogitems" and took it from there, so using an OData source with this url: 

localhost/reports/api/v2.0). I find the GUID there and used it to create this URL:

localhost/reports/api/v2.0/datasets(2b8f51be-3839-48a3-8dbd-9211c3af2c3b)/data (this is from what I can read from the instructions https://powerbi.microsoft.com/en-us/documentation/reportserver-access-dataset-odata/). However the result I get is just a single column with a lot of GUIDs.

I think these instructions can be improved as well as the whole process. However: How can I get this work?

[ErockF]

I know this is old, but I didn't see any explaination of solutions out there. I just had this issue and I solved it. The "More Columns" in advanced is important when creating your odata source. Then you get two columns in the Power Query Editor, ID and More Columns. In the header of the More Columns, to the right of the text there is a button that looks like two arrows going up then pointing away from each other (ie [↰↱]).

Clicking on that button, the Power Query Editor will open a window asking you what columns you want to include in the expansion with all selected by default. Hitting ok on that window adds all of the columns in the More Columns/Record cells to your Power Query Editor table. The M function the button calls is Table.ExpandRecordColumn. After expansion with the button, your code in Advanced Editor will look something like this. 

let
    Source = OData.Feed("https://sample.com/Reports/api/v2.0/datasets(DatasetID)/data",null, [MoreColumns=true]),
    #"Expanded More Columns" = Table.ExpandRecordColumn(Source, "More Columns", {"ID", "TITLE", "PRIORITY", "IMPACT", "MODIFIED", "CREATED"},

in
    #"Expanded More Columns"

 Hope this helps the next person who runs into this issue. 

TL;DR: The More Columns column is expandable via the button in the column header. [↰↱]

[mgmeyer]

Couple questions:

- If you execute the URL in the browser do you see all of the data your expecting to see?

- When you say you only seeing a single column is that in PBI Desktop? You should see another column like 'More Columns' if so you expand that to get the other columns in the dataset.

This particular OData API uses Open Types which is like Dynamic columns so when a query is made to the server it can't get the metadata to build the schema for the table. After the query is execute the column information comes back.

[stretcharm]

Make sure you click advanced and select Include open type columns to get all the data.

or you can add the MoreColumns in M

Source = OData.Feed("http://MyServer/Reports/api/v2.0/DataSets(06ffbf58-b11a-47d0-b17c-18ff44d26c68)/data", null, [MoreColumns=true]),

[TePe]

...so ad least the "include open type" checkmark didn't help... Didn't try the M-Code, yet...

[mgmeyer]

Did you try to execute the URL in the browser to verify that the columns and data that you want is being returned?