The case from one of my previous post had another catch! As I mentioned the client was downloading data from an API. The client was interested in data for multiple entities i.e. had to use different credentials for the same URL to retrieve different datasets. Because the client wanted to see everything in one Power BI I had to find a suitable solution. Same as before, I turned my attention towards M language!

Note: This workaround stores credentials in a string in .pbix file! This is a potential security risk, because normally credentials are not stored in pbix. Use with caution. If you know a better way, let me know!

When connecting a datasource that requires authentication PowerBI lets you save credentials for later use. This is very handy in a big majority of the situations, however it is pretty limiting when using multiple credentials. Editing saved credentials can be performed thorugh Data Source settings. It lets the user save credentials per data source.

Solving the problem required looking into Advanced Editor generated by Power BI after API connection. As seen below PowerBI uses Web.Contents function to download the data from an URL:

 

 

Quick glance at the documentation lets me learn that obviously M gives you an option to pass headers in the Web.Contents function. That’s great! But what headers to pass? Username and password? Perhaps, but that needs a confirmation. Some internet exploration leads to Wikipedia. I need to pass a parameter called “Authorization” in format of “Basic something” . This means I need to encode my Username and Login using Base64. I would need to write a small Python script to do it. But I can solve it without it. I will go to the API URL in my webbrowser with my network monitor opened (Ctrl + Shift + E in Firefox). Get a login prompt and after logging in I will look up the right header.

After getting the data I see in the Network monitor one GET request (as expected):

Double-clicking lets me explore the headers, among which is the Authorization header I am looking for:

I copy the whole string and paste it into Advanced Editor:

 

 

This lets me connect to the datasource even with Anonymous settings. This way I can pass as many credentials as I want. In the same query or in different queries to merge them later. I hope that this solution shows you that technology used underneath the tools we use everyday is the same and is well documented.

Important note AGAIN: This approach lets anyone with the access to pbix file a way to authenticate with the API. Use it very cautiously. If you know another way, please let me know!

Thanks