Importing Files From Fabric Lakehouse Into Power BI Desktop

Connect to files and folders in the Lakehouse

If you have files or folders with files in the Fabric Lakehouse, you can import it into Power BI Desktop. Lakehouse is built on top of OneLake which is nothing but Azure Data Lake Storage Gen2. Hence, you can use the ADLSg2 Connector in Power BI Desktop to import files.

There are several ways, I will list them below:

Using ADLSg2 Connector

This uses the AzureStorage.DataLake(<file_path>) function in PowerQuery

  • Go to the Lakehouse > Browse to the Folder or File > Properties

  • Copy the URL Path (not abfss)

    • In Power BI Desktop, use the Azure Data Lake Storage Gen 2 connector and paste the URL

This also works for files available via external shortcuts (e.g. files in S3 bucket) which is great because Power BI does not have a native S3 connector. Now you can create a shortcut to S3 files and folders and import them in Power BI directly. Be aware of the egress charges. Thanks to Josh Caplan for pointing this out.

As far as I know, there is no direct way to connect the files and folders in the Lakehouse. If you use the Lakehouse connector, it will connect to the SQL endpoint . Interestingly, the Lakehouse connector in Dataflow Gen2 does allow you to browse to specific file/folder so hopefully it will be available in Desktop soon.

To Access All The Files/Tables:

To access all the files and tables in a lakehouse, use just the lakehouse URL https://onelake.dfs.fabric.microsoft.com/<workspace_id>/<lakehouse_id> in the ADLSg2 connector to access all the files and tables. Use the Folder Path to identify the table/file and expand the header to get the data. Thanks to my colleague Bryan Campbell for this tip.

Using Lakehouse.Contents

We can use Lakehouse.Contents(<path>) to import files and tables directly form the lakehouse. The nice thing about this approach is that you don't need to provide the path, you just authenticate yourself and you will be connected to the Lakehouse and from there you can browse the files, folders and tables.

For Files:

Use below M code. You can also just start with Lakehouse.Contents() and go from there

Extract the Binary for the file you want to import

For Tables

If you know a direct way instead of using the URL path, please let me know.

Did you find this article valuable?

Support Sandeep Pawar by becoming a sponsor. Any amount is appreciated!