ADLS Gen2 Power Query custom connector

[Pawel_S]

Hey everyone,

I'd like to create a custom Power BI connector to my ADLS Gen2 container. I know that there is a built in version but I wanted to make it customized or "branded" lets say. Anyway I know the basics (using SDK, replacing .mez file, etc.) the question is more about M query part.

How should I create this query to avoid the issue with "The account URL should not contain query parameters", which I got when using below function?

AzureStorage.Blobs(https://youraccount.blob.core.windows.net/containername?SAS-TOKEN)

For authentication I'd like to use SAS, which would be given by the user as a parameter when connecting to datasource in Power BI.

Would appreciate any suggestions as I've been trying to find the solution for several days already without any progress...
 

[Pawel_S]

Thanks for instant reply @rubayatyasmin. There is a small progress I would say 🙂 However, when used code from Your example I got this error message in Power BI: 

"We cannot convert the value "?sv=2021-12-02&st=20..." to type Record."
(the value mentioned is my SAS key I guess)

When I checked in function refference, AzureStorage.DataLake() does not accept SAS as an argument. Possible optional arguments are: BlockSize, RequestSize, ConcurrentRequests and HierarchicalNavigation. Not sure where I can pass SAS than...

Also, when connecting from Power BI using the given method, it asks me to provide account key. Any ideas why?

Here is my current data connector logic:

[Version = "1.0.0"]
section BDL_Powerbi_Connector;

[DataSource.Kind="BDL_Powerbi_Connector", Publish="BDL_Powerbi_Connector.Publish"]
shared BDL_Powerbi_Connector.Contents = (accountName as text, sasToken as text) as table =>
    let
        accountName = accountName,
        sasToken = sasToken,
        dataLake = AzureStorage.DataLake(accountName, sasToken),
        contents = dataLake{[#"Folder Path"="dataproducts/",#"File Name"="test.txt"]}[Content]
    in
        contents;

// Data Source Kind description
BDL_Powerbi_Connector = [
    Authentication = [
        Key = []
    ]
];

[rubayatyasmin]

The AzureStorage.DataLake() function indeed does not accept SAS token as a parameter, which is why you're seeing the error message "We cannot convert the value "?sv=2021-12-02&st=20..." to type Record."

As a workaround, one possibility could be using Azure Function or Logic App to mediate between Power BI and ADLS Gen2. You can pass the SAS token and other details to the Azure Function/Logic App, and then use that to read data from ADLS Gen2. This is more complex and requires hosting an Azure Function or Logic App, but it can provide the functionality you need.

The AzureStorage.DataLake function requires account key for authentication.

[rubayatyasmin]

Hi, @Pawel_S 

The issue you're encountering is due to the SAS token being included directly in the container URL. The AzureStorage.Blobs function is not expecting query parameters in the URL, and that's why you're seeing the error "The account URL should not contain query parameters".

You could use AzureStorage.DataLake(accountName as text, sasToken as text) function for your purpose.

This function creates a Data Lake Store data source that can be used as input to other functions like Value.NativeQuery or Table.FromPartitions. This function supports SAS tokens.

Here is an example on how you can use it:

let
accountName = "youraccount",
sasToken = "?sv=2019-10-10&ss=bfqt&srt=sco&sp=rwdlacupx&se=2025-04-14T03:21:54Z&st=2020-04-13T19:21:54Z&spr=https,http&sig=YOUR_SAS_TOKEN",
dataLake = AzureStorage.DataLake(accountName, sasToken),
contents = dataLake{[#"Folder Path"="containername/",#"File Name"="filename"]}[Content]
in
contents

In the above code, replace "youraccount", YOUR_SAS_TOKEN, "containername/", and "filename" with your actual account name, SAS token, container name, and file name respectively.

In your connector, you will need to ask the user for the account name, container name, file name and SAS token as parameters. You would then substitute these parameters into the code above to connect to the user's data. This should resolve your issue.

Remember to also handle cases where the user inputs are not valid or the requested data cannot be accessed with the provided SAS token.