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

hello everyone good morning good evening good afternoon good night whatever time it is for you uh thank you for joining us uh this is me and chris webb the famous crisp uh part of paria cat team now uh yes uh all the way from london is that where you are now just outside london yes okay neyland right uh and it is like what time is it for you now over there like 8 30 pm or after that 8 30 in the evening yes oh so not too bad not too bad okay thank you thank you for joining us uh it's such a pleasure to be with you and thank you everyone again for joining us uh before we jump into the presentation uh i just want to mention one thing about the uh upcoming power bi summit um we just announced the agenda of that uh yesterday so feel free to have a look at the website we would love to have you there unfortunately we couldn't get chris on that because he seems to be on some family planning but still i mean there's opportunity for you chris always so we'll we'll see how things go okay right uh so the connecting to uh to apis web services that's always a big topic uh everywhere we talk about power bi because there are like different apis there are different signatures of how things working and uh and that is why i think this session is going to be really popular so uh all to you chris if you can start and using yourself yes great well thank you for inviting me um as i've already been introduced my name is chris webb uh i now work on the power bi cat team at microsoft um helping customers with their power bi deployments but i've been working with power bi and power query and analysis services for a long long time now um i think raise has done a good job of putting all of my links down underneath me so please follow me on twitter and uh read my blog um a lot of the content i'll be talking about today is things that i've already blogged about in more depth okay so let's get going first of all let me share my screen something to mention about today's cool is that absolutely no slides are involved um for a subject like this for for web services i thought actually it's much better to do some real demos um i also want to avoid doing really boring um just typing code type demos um there are going to be some demos like that because you can't really completely avoid it um but i want to at least kind of give you an introduction to this subject without doing too much of the really boring nitty gritty detail so what are we going to cover we're going to cover just about everything you need to know about um connecting to web services from power query inside power bi uh pretty much everything that i say here is also going to apply to power query in excel or power query online data flows or all of the other places that some power query appears but i'm going to use power bi desktop for all of my demos and really what we're going to do is we're going to talk about we're going to do an introduction to connecting to web services this is a subject that i could probably talk for about two or three hours on uh if i do do that you'd probably get very very bored indeed so i don't want to subject you to that but i want to give you an introduction to the subject to at least get you going and get you past all of the common issues that people run into with this so we're going to talk about um using the ui to connect to a web service we're going to talk about writing some code using web.contents we're going to look at guess and post requests we're going to look at how to troubleshoot web services we'll look a little bit at odata web services and we'll also have at least a bit of a mention about writing custom connectors okay so let's get going all right so the first thing to talk about on this subject is to at least say what a web service is i'm sure the vast majority of you know what a web service is you probably wouldn't be attending a session like this if you didn't but it's always worth defining some terms and for people who don't know what web services are this can be a a very frightening technical topic you hear the term you get told all you know you need to get your data for power bi from a web service but what is a web service well most of the time in power bi we're connecting to data sources like files or excel spreadsheets or databases and a web service isn't really very different in power bi terms you're probably quite familiar with the way that power bi can connect to a web page and scrape data from a table in a web page that's something that you can do but to be honest it's not all that reliable because if you're connecting to a web page you're connecting to something that is formatted for a human to view rather than an application like power bi to get data from whereas a web service is really and it's if you're giving a really simple definition a web service is something like a website which is formatted for computers for applications to get data from now the world is full of web services of different shapes and sizes um the real challenge for doing a demo a session like this is where to get a web service that is simple to use that covers pretty much everything that i want to demo it's free and doesn't need any fancy difficult authentication now it's surprisingly difficult to find somewhere like this but um what i will say is what i'm going to do is for most of my demos in this session use this site i found it fairly recently it is a really basic site intended for people who want to test their own web services and there's really not that much here it's got um a little bit of text and it's got some links to some end points that you can use so this is going to be the source of most of the web services that i use in this session now we've got a site here we've got a web service and here's one endpoint called posts and if i click this link you can see that there is some dummy data in my browser this is a web service this is one that i'm going to use for a lot of my demos and you can see that it's returning a lot of data in json format now if i want to connect to this web service in the power query editor window in power bi desktop i can simply take the url here copy it let's head over to power bi desktop i've got power vi desktop open i've got an empty power bi file here i've got the power query editor window open here i've got a blank query but we can ignore that for now and to connect to this web service i can actually do so using the ui so i go to new source web and this says import data from a web page but this will also allow you to in very simple scenarios connect to a web service click this i'm going to paste the url in now there is an advanced tab here and it's worth mentioning a few things you can do here so this is the entire url that i want to connect to i could actually break this up into multiple different parts um as i'll explain a little bit later you probably don't want to do it this way and there are some options that i can set here so for example there is a timeout that i can set to say well let's wait a certain number of minutes to get a response from the web service because if we're connecting to a particular particularly slow one maybe you don't want to wait for it ever ever and ever but i'm going to click ok and then power query does some of its usual magic what's actually going to happen is several things as well as connecting to the web service we end up with a query here that is at least four steps long and we get a nice table of data which contains basically the values that we saw here in the browser turned into table form let's understand a little bit about what's going on here in the first step two things have actually happened i have connected to my web service here and the response the thing that the web service has given me back has been interpreted as a json document it's data formatted in json format and power query has taken that it's converted it to a table it's decided that yes we probably want to expand this column and here we've got a table with all of the data in and as always it's set the data types on the columns based on what it sees in the first 100 rows of the data or so so we've connected to a web service and we've done so using the ui the bad news is that pretty much all in pretty much all cases you're not going to be able to use the ui you're going to want to be able to do something slightly more complicated which means you're going to have to write code but the good news is actually writing that m code is not too difficult you will need to know a little bit about the m language and time doesn't give me i don't really have the time here to give you a kind of complete introduction to the m language i've done various different sessions on this and there are recordings of me doing uh kind of one hour introductions to where i'm available on the internet and there are lots of other people who've done something similar so the first main bit of learning is if you want to work with web services you're going to need to write some m so it might be a good time to go away and start learning m and in fact this is probably one of the few areas in power query where you will really need to know some m but let's have a look and see what we need to do to call a web service in m to call a web service in m you need to use a special well you need to use the web.contents function the web.contents function is in the vast majority of cases uh the function the m function that you'll use to call web services um and you can see that there are lots and lots of different options here some of which we are going to talk about in more detail later on but let's go to my blank query here let's open up the advanced editor and let's write some code i'm going to just zoom in here i've got some empty code at the moment this just returns a blank string and what i'm going to do is type web dot contents if i can spell it properly and in its simplest form you only need one parameter it's a tech bit of text and that is the url of the web service that we want to connect it so here we go there is my one line of code written click done oh actually i've made a spelling mistake because it should be web dot contents there we go there we go and we've called the web service you can see that again we've got something returned this is what you see in power query when a file is being returned by a web service in this case it's being interpreted as json which is correct i can double click on that and we can go through the same steps that the power query editor did just for me a moment ago automatically so i can turn this into a table i can use the expand option to expand all of these nested record values and i could go in and set the data types on these columns so this is calling a web service it's important to understand but what we've done here is we've made a get request to a web service there are two types of requests you can make in power query you can make get requests and post requests there are in the world of web services there are actually several different methods you can make beyond get and post but power query only allows you to make get and post requests get requests are primarily intended for getting data from a web service um post requests are really intended in in development terms for updating data on a server hit some somewhere but in fact there are lots of cases where making a post request just to get data is a bit more convenient and a lot of people build more complex web services and require you to make post requests and i'll show you how to make that do that in a moment but there we are we've made our post request so we've successfully called our web service now from a power query developer's point of view um something that's very easy to get confused with when you're doing more complex things with web services is to actually understand what type of request you're making or indeed quite often what url you're calling so if you're dynamically generating things in code you're passing in values and functions it's quite difficult to actually know what you're calling in your web service you know what data you're passing back what url you've generated and so it's important to be able to see that and to be able to debug debug that and so what i'm going to do here is i'm just going to show you how you can see what power query engine is doing behind the scenes and this is going to involve using the diagnostics tools which are available up here window here so these are the query power query query diagnostics tools um there are two ways of using the diagnostics tools you can click on a step and say just diagnose that step or you can start diagnostics refresh your preview refresh your queries when you're loading in and stop and look at things but the simplest way is just to use this diagnose step option here and that's what i'm going to do so what i can do to see what happens behind the scenes when this query executes i'm just going to right click on this step and choose the diagnose option here on the menu when i do that the query preview here will be refreshed and while that happens power query will write some data some diagnostic log files on my pc and then it will also build some queries that allow me to see what's been stored in those diagnostic files now you will get perhaps a different number of these diagnostic queries automatically appearing here depending on what settings you've got set up but for the purposes of diagnosing or looking at what happens with web services the most interesting one is going to be this detailed option up here and what we've got here is a large amount of data giving us a an insight into what happens behind the scenes in the power query engine what we're really interested in is just our call to a web service and you can see here that on line 9 we've got something that tells us we're hitting a web data source we've got an operation called get response and if we scroll over to the right for this line in the data source query column if i click on this cell up here we can actually see a number of useful bits of information we can see that we're making a get request we can see the full url where um we're hitting and we've got a little bit of information about the response that we got back not an awful lot it's also possible to use tools like fiddler if you want to see in more detail what's going on but in a lot of cases for debugging purposes just using the query diagnostics options like this is going to be all that you need all right so let's delete all of that because we don't want that anymore as i mentioned this is a get request the vast majority of requests you make will be get requests but you may also in some cases want to make post requests and a post request is a request where you take some data send it to the web service and get something back now get requests can kind of do that already because get requests allow you to pass data through the url that you're calling post requests allow you to pass perhaps more data and give you more flexibility because you pass data in a different way now in order to make a post request you need to make a slight change to what you're doing in web.contents so what i'm going to do is delete all of this and i'm going to just paste in some code that i've got here the same url that i showed you before can also be used to make post requests and in order to make a post request using web.contents you need to specify a second parameter to web.contents and that's that here the second parameter enclosed in square brackets takes the form of an m record a record you can think of as being like a table with um one row and just kind of field names instead of columns and what i'm saying here is i want to take some content and as soon as i specify this content which in this case is a bit of json and i want to you know specify this content option in the second parameter of web.contents and web.contents will make a post request rather than a get request now in this case the web service is expecting a bit of json for its content and i can write that quite easily here i've just got a bit of text that represents some json um the content function expects not actual text but binary data so i'm using an m function here called text to binary that will turn this bit of text into a binary value pass that to the content option and then as soon as i've got this i can click done and again i've got a json document back i've got a slightly different response but you can see that we've now made a post request instead of a get request post requests in power query are very limited um you are well there are a number of limitations but perhaps the most serious is that you can only make you can only use anonymous authentication with post requests we'll talk about the different types of authentication you can use a little bit later on but with post requests you can only use anonymous authentication when you're working in the power query editor and that is a big restriction we'll come back to this topic a little bit later when we talk about custom connectors so we've made a post request another little side point here is that if you're generating a complex json value then putting that together as a bit of text can be quite fiddly in the way that i've done here it's also worth mentioning that the m language has much better ways of generating json values so what i'm going to do is i'm going to paste in a new version of this query which does exactly the same thing in a more elegant way so rather than actually writing the text here i'm going to use the json dot from value function which is an m function that allows you to take various different m structure types like in this case a record and it will take that and it will just convert it into a binary json value so this would make this is a much more convenient method of generating json for passing to web services for example so that's post requests we'll put post requests on the side for a moment and what we'll do now is go back to get requests and talk about more complex types of get requests so let's have a look at another query here so this is fairly simple this is just another single url it's making a get request you can see the url in this case is maybe a little bit more complicated so i've got slash comments on the end and then i've got a question mark post id equals one now if you ever looked at the bar the address bar in your browser you'll have seen something very much like this it's a very common format of url but basically what we've got here is something called a query url parameter and this is one of the ways you can take data and pass it out pass it back to your web service if you're making a get request url parameters um generally come after a question mark in your url and then you have a assault a whole series of names parameter names and values and here i've got a single parameter called post id and i want to make that equal to one and if i do this what i'm gonna get back is actually i won't do this let's just take the do this up here and you can see that this just simply says give me actually this is slightly different but it just gives me the post with the user id one what i wanted was the one with the post id one here's the one i wanted so it's basically filtering the data that we returned in the earliest demos and only give me the rows where post id equals one you can see i've only got post id equals one here and this is the parameter that for this particular web service applies the filter and as you can imagine it's this kind of thing these values these queries that you want to generate automatically you want to kind of generate in code now you could do this using string concatenation um you can imagine how you could rather than writing a single bit of text here you could kind of basically concatenate multiple bits of text together but there is a better way of doing this a better way in terms of actually ease of programming and also a better way in terms of actually making things work so if you bear this url in mind for a moment what i'm going to do is show you a new version of this query which returns exactly the same data calls the code in exactly the same way it seems a little bit more verbose but there are a number of different benefits to this so here what we've got is another example of something we can do with the second parameter of web.contents now before when we saw the second parameter we use the content option there are two other different options here relative path and query and these make it very much easier to put together a more complex url basically once you've got the root url like we've got here if you include the relative path option then that gives you something that you can just simply tag onto the end here so this would be a way of adding a forward slash on the end of this bit of text and adding comments onto the end of that and then here the query option takes the form of another record and inside this record you can have a name which will be the name of the query parameter that we want to tag onto the end of the url and the value that we want to add on to that so this will result in exactly the same url as we had before which is like i said this there we go that's the url that all of this code will generate now as i said using the relative path and query option seems a little bit verbose um from a developer point of view you'll probably find these quite convenient because they make it a lot easier to build more complex urls but there is something else even more important now like i said when you're generating a url like this quite often what you want to be able to do is dynamically generate urls and perhaps call the same url with different perhaps post id values let me give you an example of this this is maybe a little bit more complex m code but i will explain roughly what it does what i've got here is a table and this first step here creates a table it's going to have a column called id in and three rows one two and three the second step declares a function and you can see that it takes the id as text and then the function itself just simply calls web.contents with post id tagged onto the end and then i'm just doing some simple string concatenation to put the id in and then the final step says take the posts table and then for each row of the posts table call my function with the id on the current step so if i do this and we run it what we've got is our table and these binary values are three json values call created that we get by calling that function three times so this seems pretty straightforward there is a problem with this though and the problem is if you try to put this in the power bi service you would find that even though it works perfectly okay in power bi desktop it does not work in the power bi service you will not be able to refresh your data set if you do this this is one of the most common issues that people run into with web services why is this well let me explain a little bit what happens inside the power bi service inside the power bi service every time a data set gets refreshed before that data set refresh happens the power bi service actually checks to see whether the data sources used by the data set are actually there and accessible and that we have all the necessary credentials for them how does it do that well it doesn't actually run the query because this is a check that it does before the query runs what it does is it actually scans through the code and looks for places in the code where a data source is being accessed and it looks for the source of the data so in case of web dot contents it will look for the url you want to call and it says ah do i actually have all the necessary credentials to call this web service the problem is if you are dynamically generating the details to connect to your data source then the power power bi service can't actually do that check because it can't see what the data source is and this is a great example in this case what's the url that we're going to call well we can't work out what the url that we're going to call is until we run the query because we're dynamically generating the url so we've got a problem now this is a this is something that happens with um just about every different data source not just web services but it's most commonly encountered with web services and there is a way of getting around it using the relative path and the query options it turns out that if you do not do what i've done here so if you do not just concatenate everything together into one big url but if you use the relative path and query options you can get around this limitation let me give you an example of what i mean so here is the same query rewritten to use the query option i'll zoom in and it has the same results but instead of adding question mark post id equals and then doing some strength encapsulation at the end i'm using the query option as we just seen and the good thing about this is that it fools the power bi service so that when it does its analysis of the m code before the refresh takes place it just looks at the url in the first parameter of web.contents and if it can access that and it's got the um credentials necessary then it won't actually go any further so it'll think oh yes well i can access this this web service it doesn't matter that we're generating the relative path or the post id the query parameters uh dynamically as long as it can access the root the the url that we're putting into the first parameter of web.contents then it'll be fine and then refresh will happen so this is the way around it one other thing to mention um we did mention authentication a little bit earlier so far all of my demos have just worked because i have already authenticate against this web service and if you know about power query and the way power query handles authentication you will know that the power query engine once you've authenticated to a web to any data source it will remember your credentials so the next time you go to that data source it just knows them in advance but let's see what the options are for authentication because there's a few things to say here what i'm going to do is go to my data source settings and i'm just going to completely clear permissions here and now when i refresh what should happen well done this happens before clear permission okay actually let's edit that so yeah i think there's a little a bug there and clear permissions wasn't clear in the permissions but what we've got here is basically the same dialogue that would turn up if i was authenticating for the first time in this case i'm using anonymous access because there is no authentication required i've got options for using windows authentication basic authentication which involves sending a username and a password which is not very secure we've got a web api option which i will show you in a separate option but a lot of web services require you to have a key which is similar to a kind of pass word which you will either pass through the url or a number of other different ways and this web api web api option seems very tempting but i i'll i'll show you in a moment and show you why it's work as well as you would like and then finally we've got organizational account which allows me to sign in to do for example uh using azure active directory to authenticate let's go to another power bi file for a demo of this authentication um this is a more complex demo it's always good to show some nice visual demos on a boring code mostly code session like this this is a demo that i put together last year to show calling the azure maps web services and this is in particular the get root directions api now there are two different there are a variety of different ways of authentication but one is by using a subscription key and if we have a look at the documentation here you can see that when i'm making my request there is a subscription key query parameter i can use and there is that's where my subscription key would fit in the subscription key for this api you need to sign up for the service in the azure portal and get one now this subscription p key is quite sensitive and you can imagine that if you hard coded this in your m code and then handed that over to somebody else well they'd have your subscription key which is like having your password which means that they could cite you know do stuff on azure maps using your account and you'd be paying for it so that's not really a great thing and this is what the api key authentication allows you to get around if we go to transform data there's a number of fun things happening inside this particular query but if we have a look at this example up here you can see on the source step on the first line we've got our web dot contents call and we're using another option in the second parameter called api key name and api key name here i've set to subscription key which is the you'll remember the name the query parameter that um is that the documentation calls out and what this does is say that there is a special query parameter called subscription key that contains a sensitive api key name and it means that we can when we refresh this query we'll get prompted to set authentication we'll use the a web api option and we can paste our subscription key into the power query window it'll treat it like a password and it will then save it securely instead of having to embed it into text now this sounds great except for the fact that this is another example of something the power bi service doesn't support um if you were doing something in excel then great yes use this because excel is excel it's always on the desktop but unfortunately for the power bi service you might look at this and think wow i want to use this but you can't just yet hopefully we'll get around to being able to support this in the power bi service one day another form of authentication that you might hear about is award 2. in fact the vast majority of web services nowadays um will use oauth2 authentication and if you have a quick look a little bingle or use your favorite search engine to look around online you will probably see a lot of different blog posts out there telling you how you can uh use oauth 2 authentication to connect to web services inside the power query editor today i as a representative microsoft will tell you that those blog posts are all wrong and you really cannot handle all 2 authentication in a properly robust manner by doing anything in the power query editor window the functionality to support our auth 2 authentication simply does not exist unfortunately at least not in power bi desktop um awards 2 authentication sounds like just one thing but actually it's a very broad term and a variety lots of different web services implement oauth 2 authentication in slightly different ways so it's something that's going to require an awful lot of coding on your part uh it's very complex to do and really the only way of being able to achieve uh handling of old to authentication is to write a custom connector and writing a custom connector is very very complicated indeed now there are lots of um resources out there for writing custom connectors um the documentation has got lots of information about starting to write custom connectors including um documentation on how to handle awards 2 authentication but it is still an incredibly difficult topic and something that would take at least an hour for me to cover um if i was doing a session on it the good news is that there are lots of examples out there online for how to implement oauth2 authentication uh and something i wanted to call out is miguel escobar this week miguel is one of the uh one of our good power query focused mvps and um he actually posted this blog post with a link to um a custom connector that you posted on github uh recently for connecting to a very popular api which is the power bi service rest api so i can strongly recommend you to go away and have a look at that as an example of how to connect to a rest api and also it's a really handy connector to have because for example if you want to get hold of your data set refresh history or you want to get information about data sources and gateways and so on this is all information you can get at through the power bi service rest api and therefore information you can get using this particular custom connector so that's really cool in itself there is one more topic that i want to talk about with web services and that is a special type of web service called an odata web service now as we've seen connecting to web services is really a development task you know everybody implements their web service slightly differently and that means that you know this is why you end up having to write code to to access a web service so if everybody writes their web service differently wouldn't it be really handy if there was a standard for web services which meant that you didn't have to write complex code and the good news is that there is it's called odata and an awful lot of microsoft products support odata as a standard for their web services um it's an open standard um non-microsoft sources support odata as well but generally spiking in the in the real world you'll find that it's mostly microsoft or microsoft related web web services that support odata and the good thing about an odata web service is that it really simplifies how to connect to it um from a power bi power query point of view again there are some examples of odata web services the one that is available from here is called the trip pin web service and here is the root url for it if i want to be able to connect to this web service here let me just flip back to this i can connect to an odata web service not by using the from web connector although actually that will work there is a specific odata feed option here and if i do this i can pass the service route url for this odata web service and if i connect this you'll see that we've got a much nicer experience now an odata web service will expose something like a kind of database type structure in that when i connect you can see that there are a number of different tables of data that i can get and even some functions that i can call and if i was to call for example get the airports table here i can click ok and i've got a much nicer experience i will get the airports table and in a lot of cases with odata web services no m code is required at all um as an aside also with odata web services uh another nice thing to have is that um in a lot of cases query folding is supported as well query folding is the way the power query engine can take some of the transformations you define in the power query editor and pass them back and force them to be executed within the data source and query folding is supported in a lot of cases for relatively simple transformations on oh data web um web services as well now with odata web services there is a different m function that you'll use called odata.feed but it's pretty much the same as before we've got the url that we're calling we've got an options here and there are actually two implementations of h dot feed now if you've built a query in the last i think two years or so you'll get this third parameter with implementation info implementation.2 on the end if you've got old power query queries it might be worth adding this to your code if it's not already there because this can uh make your queries a lot more efficient and fast all right so that takes me to the end of my presentation um there is still a lot more detail that i could have gone into especially over web.contents but like i said i didn't want to bore you with too many things if you do want to see some of those detailed things take a look at my blog there are numerous posts on there about the details and complexities of web.contents uh but now i'd like to open up some questions great thank you it was a fantastic presentation uh i can start with some of like the most related questions like the first question was why we are using related instead of that concatenating that all inside the url which you already explained it thank you for that the next one is what if and that is coming from donald what if the api key is in the headers instead of uh url yes so if it's in the headers and indeed if it's in the url if you want this to refresh in the power bi service you will have to embed it in your code which is i know uh it's not great but it is the way it is unfortunately yeah thank you another question is that like some apis um they work with like two different tokens like you get you pass uh an id key and then you get a token then you pass another token you get another token something like that how is the process with those normally so that sounds very much like the way owauth works and this is one of those situations where you really will need to write a custom connector if you want to write truly robust code that always always works there are m functions that are only available to custom connectors um which handle these types of scenarios for um you know auth 2 and a more complicated more complicated authentication flows yeah um one like this is not much related to this topic but the question that are you going to do another book on power query that is another question um i was involved in some talks with some people some people um but realistically i i i would love to see somebody write a book on power query because um let's face it i think there there's a gap in the market some you know somebody needs to write the the m version of the definitive guide to dax um but unfortunately it's not going to be me because i am very busy at my job in microsoft and um writing books is something that is a it's a full-time job it takes a lot of time as razer as well knows uh and unfortunately i i don't have the time to write books anymore great thank you and actually that was another comment that as well that we need like a definitive guide to em like definitive guide to to dax great awesome thank you uh another question um from susana uh in terms of security do you consider the api key secure if not entered in the m code what is more secure oauth or api key probably oh i mean the other thing about to mention about custom connectors is that if you use custom connectors you have to go through a power bi gateway um there is no way of using a custom connector without going through a gateway and that adds a lot of extra hassle to um to what you're doing but i would say if you've got a choice then probably owe what is the the most secure option um there is no real way of securing an api key at the moment since uh the the web api key option doesn't work in the service great now that doesn't mean that it doesn't work uh you can do that and um you know it will work perfectly well uh there are some extra things to talk about that i didn't have time to talk about here for example there is a skip test connection option um that's related to this uh and you know if i want me to have a look at my blog post on that but um i would say if you've got a choice and you don't mind writing a custom connector go with um awards too great thank you i also mentioned that a crisp vlog is like so many content especially on power query m connectors and uh like calling web aprs and things like that definitely go and check that there one question from martha um thanks chris very useful information how can we handle different responses for the same api like for example status code 200 404 and things like that oh there is an option for that um wrong browser window if you have a look at web dot contents um there is a manual status handling option if i'd had a little bit more time this would be one of the slightly dull demos that i have done but i've got um i've got some blog posts talking about how to use manual status handling great thank you um another question which we got actually some likes on that question is that what about incremental refresh with apis and parameters how like is that supported how that is going to work it is supported um it's a bit more complicated one of the things that people get confused about with incremental refresh is that they think that query folding has to be supported uh on the data source now query folding for most apis is not supported um you can kind of write some code to support it if you want and i've got some blog posts on how to actually support query folding in your own m code uh but generally speaking it's not supported but having said that um it is possible to use incremental refresh on data sources where query folder isn't supported if you write your code correctly and you fool power query into thinking that it is applying a filter in the right way uh in fact one of the most popular posts i've written on my blog within kind of the last couple of years is a post about incremental refresh and i actually use a web service for an rss feed which is very very close to a web service as my data source in that and if you find that post that will give you an idea of how to handle um incremental refresh on web servers great thank you for that a question not much related to apis but about corey folding um from christian uh is there a workaround to add an index column and still make the query fault i think this depends on the data no well it does depend on the data source um but i think in the vast majority of cases especially with relational databases no that is that is one guaranteed way to stop query folding happening right that's that's fantastic thank you i think we've covered most of the questions it's it's a lot of discussion in the chat window so i might have missed some of the questions if anyone see the questions not answered please post it again okay another question right now is a post api can be done with odata.feed function um does odata.feed support post requests i'm not sure oh data actually has the concept of post requests so so the question is more around like uh what is the situation or when should we use web.contents versus odata.feed so i would say generally speaking if you've got a no data web service you should use odata.feed you know if your web service is odata compliant odata.feed is by far the easiest way of connecting you can actually use web.contents with odata web services but it will greatly greatly complicate things but if your web service is not oh data compliant you cannot use odata.feed and you'll have to use web.content that's great thank you uh question from scott good to see you here scott and blog post on or resources for good patterns for dealing with throttling um well yes so just to explain what throttling is a lot of web services um actually limit the number of times you can make requests to them so for example if you've got a web service and it allows you to get data you might find that if you query it more than a set number of times in a minute or a certain period of time if you query it too many times in that small time period you will start to get errors because it thinks you're trying to get too much data uh the period of time will vary but it's quite often uh something like that and this is where you've got to again write some more complex uh code using um there's the ability in m to actually delay a call to a function uh again i've got some blog posts uh on that as well in fact actually i can probably show you the function here in the docs i'll maybe dig it up a little bit later but uh it is here we go function.invoke after i suspect scott has already seen this because i think i know why scott's asking this um but function dot invoke after i've got some blog posts on this various other people have as well and this simply allows you to call a function after waiting for a set period of time and this is the key to getting around throttling because if you know that a web service only allows you to make a set number of requests within a 30-second period if for example you then add a 10-second delay in between each one of your requests then you can pretty much guarantee that you'll never hit that throttle because you're never making more than one request in in a 10 second period great thank you for that uh i mean this is not a question from here but i think would be good if you go through like a very brief explanation of uh like the overall execution pattern of a power query script like uh or i can ask it like in this way uh do does all the lines of code we have in power query script run sequentially under thread or they would run differently just very briefly if you can explain um it's complicated and um i don't have time to go into it here but one thing that actually is very relevant to this topic and is probably worth mentioning is that the reason that power query doesn't really give you much support with post requests is that power query as a rule should never be used to update a data source power query should be used to get data from a data source but you should never update a data source and like i said post requests do allow you to in a lot of cases update a data source the problem with a power query query is that in a lot of cases when you're working in power bi desktop a data source will be called a data source will be um hit more times than you might expect and this is especially true with web services when you run a power query query especially in the power query editor you might find that even if you even though you just run the query once the web service gets hit multiple times um and this can happen for a number of different reasons there are kind of internal evaluations and things that cause it to happen but this is also why you should never use power query to update a data source uh including using post requests to update a data source so it's complicated and unfortunately i'll have to kind of point you to my introduction to m session recordings which are fairly fairly easy to find so that you you kind of understand a little bit more about what happens when an m query runs great awesome thank you for all the answers i believe we covered all the questions a lot of comments saying that thank you it was great presentation uh and it was definitely fantastic presentation the recording is available here for uh everyone to watch later um and if you have any questions feel free to write in the comments later on we'll uh somehow pass it to chris uh and ask for his advice thank you again chris any last words to say well no just thank you for listening and thank you for using power bi fantastic thank you uh have a good rest of your day everyone and goodbye thanks a lot bye bye

Channel: RADACAD

Views: 16,131

Keywords: Power BI, Power Query, API, Web Service, Get Data

Id: a0FqNLI0VsQ

Length: 57min 12sec (3432 seconds)

Published: Fri Feb 26 2021