Fabric content is all over the place by now. In Fabric, as a SaaS platform, most (if not all) services have interconnectivity. In a few clicks you connect your web-developed Power BI dataset to a lakehouse, or warehouse to fetch data from OneLake. But what about Power BI Desktop? You might have uploaded some files to OneLake which you cannot access from Power BI Desktop.

In this blog I’ll explain on how you can connect to OneLake data using Power BI Desktop!

How is this useful?

There can be various scenarios in which it is useful to work in Power BI desktop with data from OneLake (lakehouse and warehouse) directly. There are a few typical examples I can think about:

• Web modeling capabilities for Power BI datasets are limited today. Power BI Desktop offers a wider range of features.
• Via the OneLake file explorer you might have uploaded data, which you normally would have done to OneDrive or SharePoint, which you now want to put in OneLake.

Especially the second scenario described above can be interesting. By saving your data to OneLake, you can easily make it accessible for others to, to deliver up to the promise of OneLake. In the past with OneDrive or SharePoint, this required you to spin-up a site or team which might have different governance and your data will be all over the place.

Lakehouse Connector

Today, if you work with Power BI Desktop, there is no connector in the Get Data menu to connect to OneLake, lakehouses or warehouses. So, how do we get this to work?

While I was trying out some other features, I wanted to connect to my data in OneLake. Within a lakehouse in Fabric, you have a native option to browse through files as part of the explorer.

Of course, I could have started building my Power BI dataset directly from the top ribbon. Though, I wanted to connect it to Power BI Desktop given I wanted to play around with the new Power BI Project Files as well, properly commit to a DevOps repository and get the best of all worlds together.

But hey, there is a preview connector in Power BI Desktop right? Well yes, from the top ribbon in Power BI desktop you can connect to lakehouses, warehouses, existing Power BI datasets and datamarts.

However, to my experience this solely showing the tables in your lakehouse, which is basically creating a live connection to the auto-generated Power BI dataset. Although you clicked a button to connect to the lakehouse, it actually fools you and makes a connection to the Power BI dataset that comes with it. If you want to try this out, notice the connection string visible on the tables in the model view of Power BI Desktop after making a connection – it shows you the Power BI API endpoint.

Also, in my case I just wanted to grab the files I manually uploaded to OneLake. From the lakehouse, I managed to see them, but not yet to connect to them in Desktop. By accident I stumbled upon the connector in Dataflows gen2 – as part of Fabric. I noticed Dataflows gen2 offers some connectors that are not present in Power BI Desktop (at the time of writing – August 2023), so I went for it!

I figured that there is a new Power Query connector Lakehouse.Contents which is not in the interface. The connector requires a two parameters:

• WorkspaceId
• LakehouseId

These parameters can easily be fetched from the URL in the Fabric SaaS service. Based on these parameters, you get to see an overview of all your content to which the lakehouse has access, which includes a section Files.

When we expand the Files section, we got to see all files that were manually uploaded to the OneLake folder of this workspace (under the covers, the related storage account). In case you have multiple folders uploaded to OneLake, you need to specify the folder name as well in the connector before you got to see all files.

From here on, it works like any other connector, and you can expand all files together, enhance your Power Query logic etcetera.

Wrap up

Although the connector is available in Power BI Desktop, there has not been any blog post or release notes about this setup as a feature. Therefore, I advise you to take this approach as unsupported, but working. Obviously, we can expect an OneLake connector to be added to Power BI Desktop in the future.

On the long run, I can imagine uploading flat files to OneLake becoming a better alternative than uploading them to SharePoint or OneDrive. Especially since it fits well in the vision of OneLake and Fabric as a whole to reduce data duplication. With OneLake you can centrally store this data, with less complex access management and less services needed.

For everyone that wants to try this out, I’ve added OneLake Files.pbit (Power BI template file) to my GitHub repository for Power BI Examples. By opening the template file, you musts fill parameters for workspaceId, lakehouseId and folderName, after which you’re prompted to authenticate.

For Authentication you can use the default Microsoft authentication and sign in with your organizational account.