Straight to the point: I worked with API which required first login via POST method and after successful login returned cookie to be used in GET request. This proved to be rather difficult (impossible?) task for Power Query. I have struggled with this kind of problem once before. In that case API required md5-hashed key and I ended up building SSIS-integration and separate DW for that case.
This time DW would have been definitely overkill since the data amount I wanted from this API was rather small.
After some research I ended up using http triggered Azure function. Here’s how it works.
Http triggered function means that function is executed only when I use direct url for function. Function contains C# where I handle authentication, get cookie from response and then use cookie to get data via GET request. And lastly function returns data (in this case JSON) as a result.
So basically all that’s let for Power query is to call our function url using Web.Contents and then to modify data as needed. Function url contains app key and it’s also possible to force user to use Oauth2 authentication also.
Here’s how to:
- Create Http triggered function
2. Write your code to execute the magic
3. Return response data from your api response
4. Test run your code
5. Copy function url from Azure and use it in Power Query
And there you go