Azure Table Storage in Power BI with a Shared Access Signature Token

TLDR; PBI does not officially support SAS token, using the REST API and generating continuation tokens ourselves we can achieve this. Below you will find the M-script.

I recently got the question to retrieve data for a Power BI report from a Azure Storage account. As it is an Microsoft technology I couldn’t imagine that it would not be possible to read from it in Power BI, but playing on the safe side of things I went to check Power BI.

Sure enough Azure Table Storage was listed in the PBI connectors

I entered the SAS token I was provided with, but that gave me an error message pointing to the length of the URL. I ended up removing the query from the URL. Next it prompted me for an account key, I tried our SAS-signature, no luck.

Why we don’t want to use an account key

Storage accounts in Azure have two ways of providing access

• Shared Access Signature tokens (SAS)
• Account key

With SAS it is possible to specifically grant access to the storage with options like token expiry, permissions (query, add, update, delete) and even based on partition and row keys.

Account keys on the other hand grant full access to the Storage account, including blobs, queues, etc.

Giving out full access to everything for the purpose of reporting is obviously overkill and some organisations plainly refuse to give them out. As should they.

How to make a SAS token work

The most obvious solution is using M’s Web.Contents function to call the URL and using the API retrieve the contents of the table. But you’ll notice, as I did, that the Table Storage REST API has a limit of 1000 rows to return per request. The API makes use of some kind of pagination where the rest of the results can be retrieved in subsequent requests. The API requests returns headers with continuation tokens which we should provide in our next request.

x-ms-request-id: f9b2cd09-4dec-4570-b06d-4fa30179a58e
x-ms-version: 2015-12-11
x-ms-continuation-NextPartitionKey: 1!8!U21pdGg-
x-ms-continuation-NextRowKey: 1!8!QmVuOTk5

Now, as you might know we are unable to retrieve the response headers from a request (upvote), in this post a Microsoft employee states that this is by design for security reasons.

After looking at the keys I quickly discovered the pattern that was ‘hidden’ within. NextPartitionKey and NextRowKey a both structured the same.

1 ! 8 ! U21pdGg-
↑ ↑ ↑ ↑ ↑
| | | | Base64 encoded row/partition key
| | | Separator
| | Length of base64 encoded key
| Separator
Static prefix

Now with this knowledge we are able to reconstruct the tokens ourselves, I created a recursive function in M that makes a HTTP-request to the endpoint, takes the last item in the list it retrieves, constructs the tokens with the structure above. In the end all results are merged into one table. Now, recall that the NextRowKey and NextPartitionKey refer to the next item to retrieve and since our function use the last item we always have an overlap of one row with every request; Table.Distinct to the rescue!

Show me the code already!

RecursiveTableStorage — Function you should call from other queries

Now here’s how to use this function. Separate your query from your URL and store them in a record, don’t forget to do a URI-decode on the query values :).

How to use RecursiveTableStorage function

Now while this may work very well for us, it is just a headache that SAS aren’t supported by Power BI, there has been an idea posted up for a few years on the PBI ideas board, so all do upvote this! Now Microsoft, it is your move to make this work-around obsolete. If you liked this, don’t forget to leave some claps.

PS: If you experience any issues with Scheduled Refresh using this method please see my most recent post.