How to implicitly add KEY or SAS to an azure blob call using Power M query

[walid140891]

Hello Power BI Pros,

We have been through this for weeks now, we want to pass a key of a blob storage account implicitly in power query while calling the azure blob storage by url, the idea behind this is that we don't show the keys for customers when they publish the power bi file and that they won't be obliged to enter the key since we don't want to reveal it.

The strategy for the moment is, we have an azure function that creates a temporay SAS for the blob storage on power bi refresh and we want to pass that SAS to the url on call.

// Can we, somehow, pass a key or a SAS here instead of doing it in power bi desktop or online service on credentials setting?
let
    Source = AzureStorage.Blobs("https://xxxpowerbistorage.blob.core.windows.net/11123456-0000-000x-f123-44e1e7959c56"))
in
    Source

Thank you in advance.

[Anonymous]

To pass a SAS token to the Azure Storage Blobs connector in Power Query, you can use the following syntax:

let
Source = AzureStorage.Blobs("https://xxxpowerbistorage.blob.core.windows.net/11123456-0000-000x-f123-44e1e7959c56?<SAS_TOKEN>")

Replace <SAS_TOKEN> with the actual SAS token that you want to use. The SAS token can be generated using the Azure Storage SDK or Azure PowerShell cmdlets, or it can be obtained from an Azure Function or other server-side component.

Keep in mind that the SAS token should be URL-encoded if it contains special characters such as ?, &, or =. You can use a tool such as this one to URL-encode the SAS token: https://www.urlencoder.org/

Thanks and keep me in your prayers.

[walid140891]

Hello Farhad,

Thank you for your response, in order to test your solution, I simply generated a SAS token for the container and used the URL-encode to encode it, but when I add it after the "?" in the main url I get this error: dataformat.error the account url shouldn't have query parameters.
Do you think that the method AzureStorage.Blobs(...) doesn't allow parameters in queries?

[lbendlin]

Keep the key in a separate source (for example a sharepoint based text file)  and then fetch it as part of the query.  Be prepared to have fun with the formula firewall.

[walid140891]

Hello @lbendlin , thank you for your answer, I appreciate it! Would you please explain what do you mean by the formula firewall? 2nd question is: even after fetching the key how can I use it in the AzureStorage.Blobs(...) method? How can I prevent powerbi from asking about the key explicitly after publish!!!

[lbendlin]

Behind the scenes of the Data Privacy Firewall - Power Query | Microsoft Learn

Please try my proposed approach - Power BI won't be asking.

[walid140891]

Thank you! but, again, let's suppose that I can get the key or SAS from whatever source, how can I pass it to the blob storage url while calling it? I cannot find a technical way to do that!