How to get data from a Fabric Lakehouse File into Power BI Desktop – Using Scanner API JSON
How to get data from a Fabric Lakehouse File into Power BI Desktop – Using Scanner API JSON
In this blog post I am going to show you how I connected to my Scanner API JSON file which is stored in the files section of my Microsoft Fabric Lakehouse.
Full credit on how to complete this comes from Marc’s blog post https://data-marc.com/2023/08/25/access-onelake-files-from-power-bi-desktop/
This is where my Scanner API JSON file is stored in my Microsoft Fabric Lakehouse
Getting the WorkspaceId and LakehouseId
The steps below I will show you how to connect to the files in your Lakehouse getting the workspaceId and LakehouseId.
- The first thing you need to do is to get the Workspace ID and Lakehouse ID.
- To do this I navigated to my App Workspace where my lakehouse files are and then went into my Lakehouse. As shown below where I am in my Lakehouse.
1 – This is where I clicked on my App Workspace
2 – This is where I opened my Lakehouse called “FM_LH”
3 – This is where I can see the details of my Lakehouse.
- Now in the URL in my Browser I will copy the details as explained below.
- 1 – This is the WorkspaceId
- EG: cb2af739-998e-bbbb-aaaa-f78d2e8fc1dd
- 2 – This is the LakehouseId
- EG: 260cc545-9a59-aaaa-bbbb-fb705d4bc4ae
- Keep a note of the WorkspaceId and LakehouseId
Connecting to your Lakehouse Data
In the steps below I will show you how to connect to the lakehouse data using the PBIT.
NOTE: Please make sure that you have followed my previous blog post Downloading Scanner API data using a Microsoft Fabric Notebook where the Scanner API JSON file is in the folder called “Scanner_API”
- Download the Blog – Get Power BI Scanner API.pbit
- When the PBIT is opened it will prompt you to put in the WorkspaceId and LakehouseId as shown below.
- Then click on Load
- After a little while it should prompt you to authenticate.
- Click on “Sign in as a different user”
- Next, follow the login authentication based on your organization login requirements.
- Once signed in I then clicked on Connect
- Once you click the data should then load and you should see the following once it has completed.
- As shown below it should get the JSON file and load it.
- If you are interested in how, it gets the data, as per Marc’s blog post It is using the Power Query connector “Lakehouse.Contents”
- And then the WorkspaceId and LakehouseId is being passed as shown below.
- Which when looking at the Power Query Applied steps, I can then see all the folders and files in my Lakehouse.
Summary
Thanks for reading my blog post I hope that you now know how to get data from the Lakehouse files.
Here is what can be achieved from using the Scanner API data when looking for related items in Fabric shown below.
In future blog posts I will show you how to get some valuable information from the Scanner API data.
[…] Gilbert Quevauvilliers hunts for some output files: […]
Hi,
I’ve followed all the steps and receive an error when entering workspaceId and lakehouseId in the provided pbit:
Workspaces table – “The key didn’t match any rows in the table”
Related Artefacts table – “The key didn’t match any rows in the table”
This error is caused by the following part of the query:
let
Source = Lakehouse.Contents(null){[workspaceId = WorkspaceId ]}[Data]{[lakehouseId = LakehouseId]}[Data],
Files_Folder = Source{[Id=”Files”,ItemKind=”Folder”]}[Data],
#”Filtered Rows” = Table.SelectRows(Files_Folder, each [Name] = “pbi_scannerapi_output.json”),
#”pbi_scannerapi_output json” = #”Filtered Rows”{[Name=”pbi_scannerapi_output.json”]}[Content],
It is not finding any .json file with the pbi_scannerapi_output.json naming.
Th pbit is not totally plug and play, atleast for me
Hi there,
Can you please ensure that the file “pbi_scannerapi_output.json” has the same name?
Also can you make sure that it is the default location of Files and not in another folder.
Hi, very nice blog but unfortunaly I got the same error as Øystein Toppe Tolaas when using the Get Power BI Scanner API.pbit report .
My json file is called pbi_scannerapi_output.json from this path Files/Scanner_AP. Hope you can help.
Hi Oscar,
It might be because the location of JSON file is in a different location. If you look at the steps and go up you can see where I am selecting the location in the Lakehouse?
Hi,
Thank you for you response.
the relative path is;
Files/Scanner_API/pbi_scannerapi_output.json
is this wrong ?
all other tables are filled only this 2 not.
regards,
Oscar
Yeah is that where it is in your Lakehouse?
Yes indeed I can’t past a picture of this in here otherwise you could confirm this.
regards Oscar