Issue powerbi getting data from SharePoint Search

[Deleted]

For a Client of mine i am trying to create a dashboard of all documents in a sitecollection.

it was no problem with odata.feed. but problem with that was that i can not schedule it to refresh.

i finally used web.contents. to refresh the data this works but now i have the XML data as it does not want to read the Json of SharePoint i am in a nested table issue.

I worked a lot out and came down to this and here is my problem:

Table.FromRows(List.Transform(element1, each List.Transform(_, each Record.FieldValues(_{1}){0} ))),

the problem is that record.field values only returns 1 column instead of all my columns. does anybody have a solution so it shows all columns?

I am new to the Powerbi stuff so have tried a lot but syntax incorrect or did not work :(

hope for some answers.

Kr,

Paul

[#2]

Have you worked through this blog post's code? This might provide some hints. http://makingthings.work/connect-power-bi-to-sp-search/

[#3]

Yes did use this post as well but problem in this solution is that it uses  Source = OData.Feed(

which you can not refresh in powerBi Pro or schedule.

So needed to use web.contents to schedule refresh.

Hope there is a different solution.

thanks 

kr,

Paul

[#4]

I'm not sure why you can't refresh Odata as I use OData all of the time with Project Online OData feeds. with no issues refreshing. You have to reset the credentials in Powerbi.com after publishing as they get wiped on first publish.

[#5]

Hi Treb,

in PowerBi Desktop the refresh works but in PowerBi Pro Online it does not :(

kr,

Paul

[#6]

Hi Paul,
My experience with OData - in PBI Desktop you use Windows authorization; in PBI Online have to change it on OAuth2 (and do it each time when you update the model). But that was very limited experience.

[#7]

Hi Sergei,

will give that a try. thanks.

Kr,

Paul

[#8]

Hi Serge.

i cannot set Oauth2 in my powerbi Desktop or am i missing something?

Kr,

Paul

[#9]

In Power BI Desktop, it's the Organizational Account credentials.

To update credentials in Powerbi.com for the refresh:

• Once published to PowerBI.com, expand the left menu to see the dashboards, etc.
• Click the ... next to the Dataset you published.
• Select Scheduled Refresh.
• Expand the credentials section. This is where I see Odata.
• When I click it, you change the dropdown from anonymous to OAuth2
• Enter the credentials to access the data.
• OData will refresh afterward without issue.

Hope this helps!

Treb Gatte | Business Solutions MVP | Blog | Learn Power BI | Twitter

[#10]

sorry for my late reply i am going to check it!

[#11]

Sorry @Treb Gatte it is still not working just tested it a moment a go..

[#12]

That could be due to this

This morning i also have an issue with OData refresh as well as with some other cloud services.

[#13]

Seems you found the solution for the refresh issue in PowerBI online yourself :) Thanks for the good blog post: https://sharepoint-specialist.nu/connect-power-bi-online-to-sharepoint-search-using-rest-ca6e681233b...