Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AllanXu
Helper II
Helper II

Can I have a JSON file as data source

Greetings,

 

is it possible to have a JSON file (local or in Azure BLOB) as a datasource?

 

Thank you,

1 ACCEPTED SOLUTION
mike_honey
Memorable Member
Memorable Member

Yes - use Get Data / From Web and enter the URL (or local path) to the JSON file. PBI will interpret that file as JSON and show you the root node, which you can drill in and expand on to get to the data elements you want. 

 

The exact steps will depend on the shape of your JSON.

 

I described this process using a publicly-available JSON file and Excel Power Query (the predecessor of the PBI Get Data UI):

 

http://superuser.com/a/977376/310267

View solution in original post

3 REPLIES 3
mike_honey
Memorable Member
Memorable Member

Yes - use Get Data / From Web and enter the URL (or local path) to the JSON file. PBI will interpret that file as JSON and show you the root node, which you can drill in and expand on to get to the data elements you want. 

 

The exact steps will depend on the shape of your JSON.

 

I described this process using a publicly-available JSON file and Excel Power Query (the predecessor of the PBI Get Data UI):

 

http://superuser.com/a/977376/310267

Anonymous
Not applicable

How does this work for a deep json files with hierarchies?

 

{
"id": "0001",
"type": "donut",
"name": "Cake",
"ppu": 0.55,
"batters":
{
"batter":
[
{ "id": "1001", "type": "Regular" },
{ "id": "1002", "type": "Chocolate" },
{ "id": "1003", "type": "Blueberry" },
{ "id": "1004", "type": "Devil's Food" }
]
},
"topping":
[
{ "id": "5001", "type": "None" },
{ "id": "5002", "type": "Glazed" },
{ "id": "5005", "type": "Sugar" },
{ "id": "5007", "type": "Powdered Sugar" },
{ "id": "5006", "type": "Chocolate with Sprinkles" },
{ "id": "5003", "type": "Chocolate" },
{ "id": "5004", "type": "Maple" }
]
}

 

I wasn't able to parse the entire json file into any table structure.

For a structure like that I would work on 3 Queries / tables

  1. "batters"
  2. "toppings"
  3. "items"

For "batters" I would keep just the root id column (lets call it "item id") and expand the batters data to get multiple rows for each item id (4 rows from your sample data).  Ditto for "toppings" (7 rows).   The "items" query/table would show all the non-hierarchical columns (one row per item id, so 1 row from your sample data).  

 

In the Power BI Relationships window you can create relationships between items -> batters and items -> toppings (both on item id).  Then in theory you can combine any column from any table in your visuals.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors