Accessing API and web service data using Power Query - by Chris Webb

Speaker: Chris Webb

#PowerQuery #PowerBI #API #WebAPI #WebService #GetData

[eagillum]

6:27- the source of most of the webservices that he uses in this session (json converter)
7:45- first webservice connection
11:00- calling a webservice in M (GET request)
14:38- what Power Query engine is doing behind the scenes (diagnostics tools)
17:29- POST requests!
20:20- M is a more elegant way of writing JSON (JSON.FromValue)
21:31- More complex types of GET requests
27:00- this query refreshes in desktop, but not in service- WHY?
31:23- authentification/permission issues
33:35- Get route API! (Azure Maps) (hiding your API key)
36:33- another form of authentification- OAuth2 [sp?]
39:41- OData webservices (no M code required) (query folding supported)
43:58- questions

[wonnapee]

27:37 - it s very valuable info about pbi service and auto refresh

[gauravsukhadia]

Superb Chris Webb I was looking forward to getting around with the trick to make my API flexible with query1 and this is it. I have been through like million websites and this is the only video that showed me right solution. Awesome stuff.

[fobrutsky]

Great content, very proffesional, practical, straight ahead presentation.

[fajnefajne]

Thank you very much for the lecture by professor Webb on the science of Power Query. :-)

[meliudaj]

Topic perfectly on point of my current project (especially the follow up questions)... in summary MS. Excel simply can't connect to any modern REST API services... (modern implying now common secure authentication methodologies)

So to connect Excel to majority of the internet, one HAS to write their own custom connector.

Gotta say this is a painstakingly obvious shortcoming of Microsoft it seems.

[ThepExcel]

Thanks a lot for streaming this content

[johng]

Thanks in a million. Great content. Awesome. Grade: A++💥

[scramiro]

This was amazing. More content around scrapping data on a web please. Can I ask how do you know if a webiste is OData?

[prabhatmuneshwar]

Hi Chris, thanks a lot for the detailed video. The part where you explained about getting around with dynamic refresh error.. In that query, you defined a table with values as 1, 2, 3 for Id in the the query itself.. I'm trying to refer it to an existing list rather than defining the table in the query itself.. How can I achieve that?

[Broadmoore]

Thank you for this video! I’m finding that when I do this dynamically that the API is being called multiple times rather than just once per row. I’m not referencing any other queries, just table data. Cache maybe? Any ideas on how to avoid it calling the web api url more than once per row?

[Justbrowsingsoflo]

I'm having an issue with calling usda data.
One of the query parameter includes spaces. If I typed the query direct into browser with spaces, it works.
When using web.contents the spaces are escaped it seems, and the escape characters become part of the url.
In doing so, the api call doesn't work.
Any ideas?

[lethalwordz]

Does this work for all APIs? Are there any limitations? I'd like to try to connect to the ADP API, but haven't found any actual examples

[kavyaseeramsetty]

Thanks for the session. It helped a lot : )

[surender]

Hi Please help me, How can i get data in power bi from druid