cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Power BI Desktop Get Data from ADLS Gen 2

We're currently using Azure Data Lake Store Gen 1 and are looking to transition to ADLS Gen 2.  Our data lake is solely used by Power BI Pro authors.  However, for the life of me, I can't figure out how to use ADLS Gen 2 as a source from the Power BI Desktop.  There's plenty of articles on using ADLS Gen 2 as the storage for Power BI Data Flows (primarily Connect Azure Data Lake Storage Gen2 for dataflow storage (Preview)), but not how to use data already existing in ADLS as a source.

 

We're using Azure Data Factory to load text files into ADLS Gen 2, similar to how we with ADLS Gen 1.  I've verified the security is setup and can browse, download, upload with Azure Storage Explorer.

 

Power BI Desktop Get Data from ADLS Gen 1 is relatively simple.  Get Data, Azure Data Lake Storage, Enter the URL or ADL URI from the Azure Portal Overview, then Edit and browse to the file you want.

 

Trying to do the same with ADLS Gen 2, I'm going in circles.  Maybe I'm not thinking about it in the right way.  I've tried:
Get Data, Azure Blob Storage, enter Primary Blob Service Endpoint link from ADLS G2 properties page, and get:

Details: "AzureBlobs failed to get contents from 'https://xxxx.blob.core.windows.net/'. Status code: 400, description: 'Blob API is not yet supported for hierarchical namespace accounts.'."

ABS Blob Error.jpg

Get Data, Azure Blob Storage, enter Primary DLS File System Endpoint link, and get:

Details: "AzureBlobs failed to get contents from 'https://xxxx.dfs.core.windows.net/'. Status code: 400, description: 'The request URI is invalid.'."

ABS DLS Error.jpg

Get Data, Azure Data Lake Storage, enter Primary Blob Service Endpoint link, and get:

Details: "Access to the resource is forbidden."

ADLS Blob Link.jpg

Get Data, Azure Data Lake Storage, enter Primary DLS File System Endpoint link, and get:

[DataSource.Error] Web.Contents failed to get contents from 'https://xxxx.dfs.core.windows.net/webhdfs/v1/' (400): The request URI is invalid.

ADLS DLS Error.jpg

 

Note I'm using the latest Power BI Desktop 201903 2.67.5404.581.  Also, I didn't name or enter the ADLS G2 as xxxx, I'm obscuring it in this forum post for security reasons.  We're not savages.

 

Now, before someone says "OMGoodness! Use Dataflows!!!", I tried.  Went to our Power BI Service Premium, workspace, Dataflows (preview), Create Dataflow, Add new entities, Azure.  There's no option for Azure Data Lake, so I tried Azure Blobs.

Entering the Primary Blob Service Endpoint link from ADLS G2 properties page returns:

Error from external service: DataSource.Error: AzureBlobs failed to get contents from 'https://xxxx.blob.core.windows.net/'. Status code: 400, description: 'Blob API is not yet supported for hierarchical namespace accounts.'. (Session ID: 4d1ab14c-bf3b-487c-bd52-54f842d16fe6)

Data Flow ABS Link.jpg

 

Entering the Primary DLS File System Endpoint link with Authentication kind-Account Key & the Account key from ADLS G2 Access keys returns:

Error from external service: DataSource.Error: AzureBlobs failed to get contents from 'https://xxxx.dfs.core.windows.net/'. Status code: 400, description: 'The request URI is invalid.'. (Session ID: 4d1ab14c-bf3b-487c-bd52-54f842d16fe6)

DataFlow DLS Link.jpg

5 REPLIES 5
Community Support Team
Community Support Team

Re: Power BI Desktop Get Data from ADLS Gen 2

Hi @Anonymous 

"Querying Data in Azure Data Lake Store with Power BI"  only applies to Azure Data Lake Storage Gen1.

Direct support from Power BI (or Azure Analysis Services) is not yet supported for Azure Data Lake Storage Gen2.

 

As searched, it seems possible to use ADLS Gen 2 as Azure SQL DataWarehouse External Data Source.

Please check 

"How to use data stored in ADLS Gen 2"

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Re: Power BI Desktop Get Data from ADLS Gen 2

Thanks Maggie.  Is there a plan to add direct support to Azure Data Lake Storage Gen2 from Power BI or Azure Analysis Services?

Super User
Super User

Re: Power BI Desktop Get Data from ADLS Gen 2

Hi @Anonymous ,

make sure to support the idea here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/37015567-power-bi-direct-connector-to-adls2-azure-data-lak 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Re: Power BI Desktop Get Data from ADLS Gen 2

It has now with Power BI July 2019 updates.

image.png

But as I try to use it, because in Power BI ideas said it is direct connection, so I just paste it the URL of my ADLS2 path, sign in to my organization account  which the same account as the one create the ADLS2 folder, but it give me error.

 

Here is my steps result the error :

1. From Azure  portal, storage explorer, go to property of my ADLS Gen 2 storage account. Copy the URL.

image.png

 

2. Paste it in Get Data -> Azure -> Azure Datalake Sstorage Gen2 URL path 

image.png

 

3. Sign in 

image.png

 

4. Error appeared after click connect saying couldn't authenticate.

image.png

 

Thanks,

 

 

 

 

 

 

Alex86 Frequent Visitor
Frequent Visitor

Re: Power BI Desktop Get Data from ADLS Gen 2

Hi, 

you can try the following steps:

 

1. Get the URL from Storage explorer (I used the local installation):

Alex86_0-1572424979236.png

 

2. Paste the adress for ADLS2 Connector and use the key for authentification (otherwise you have to give your account permissions).

 

Alex86_1-1572424616283.png

 

3. Get the key from the azure storage explorer

Alex86_2-1572424731002.png

 

Alex86_4-1572424856804.png

 

Cheers

Alex

 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)