Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.'."
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.'."
Get Data, Azure Data Lake Storage, enter Primary Blob Service Endpoint link, and get:
Details: "Access to the resource is forbidden."
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.
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)
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)
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.
Thanks Maggie. Is there a plan to add direct support to Azure Data Lake Storage Gen2 from Power BI or Azure Analysis Services?
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
It has now with Power BI July 2019 updates.
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.
2. Paste it in Get Data -> Azure -> Azure Datalake Sstorage Gen2 URL path
3. Sign in
4. Error appeared after click connect saying couldn't authenticate.
Thanks,
Hi,
you can try the following steps:
1. Get the URL from Storage explorer (I used the local installation):
2. Paste the adress for ADLS2 Connector and use the key for authentification (otherwise you have to give your account permissions).
3. Get the key from the azure storage explorer
Cheers
Alex
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.