How to connect to Onelake files and read an Excel file content from Dataflow Gen2

[sandeep022]

Hello, We can connect from the Fabric PowerBI/Dataflow Gen2 to an excel file on Onedrive with web based connection and read the contents of it to a table using Power Query. However when I tried to use the same method to connect to excel residing on Onelake[with let Source = Excel.Workbook(Web.Contents("https://onelake.dfs.fabric.microsoft.com/xxxxxxxxxxx], it doesn't connect and gives error "Unable to create connection, please check to see if the data source path is correct ...". So is there any restrictions as of now to connect to Onelake files directly with web based URLs? Is there any solution or workaround to overcome this? I referred this but not much helping. Tried this too but not solving the issue fully as the sheets in the excel file are not properly converted to tables. Thanks.

[sandeep022]

Hello Dino, Thanks for the response and solution provided. However it doesn't completely address and solve the problem I explained. The idea is to connect the OneLake file(An excel under Files of Lakehouse) directly from Dataflow Gen-2 like how it can get data from a Onedrive or ADLSGen2 and then apply power query on top of the data for transformation, rather than make the file to managed tables with the Spark and delta lake approach. The web url+token combo seems not working with Fabric dataflow. Now what we're not sure is whether it is a restriction/limitation by MS or is it something which a tenant admin can resolve. And for the file access locally, the Onelake explorer helps us and looks like no need of storage explorer. However, didn't get this - 'If data residency is a concern, ensure you're using the correct regional endpoint for your workspace.' How can this be ensured? Thanks!

[v-junyant-msft]

Hi @sandeep022 ,

Please ensure you're using Azure Storage Explorer version 1.29.0 or higher to connect to OneLake. This tool allows you to upload, download, and manage files in OneLake.
Integrate OneLake with Azure Storage Explorer - Microsoft Fabric | Microsoft Learn
OneLake doesn't guarantee data residency in a specific region when using the global endpoint. If data residency is a concern, ensure you're using the correct regional endpoint for your workspace. This might resolve connection issues related to regional data handling.
How do I connect to OneLake? - Microsoft Fabric | Microsoft Learn
If direct web access to Excel files in OneLake is not feasible, consider using data integration tools like Azure Data Factory or Azure Synapse Analytics for data extraction and loading.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.