Forum Discussion
Can I have a JSON file as data source
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):
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):
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
- "batters"
- "toppings"
- "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.