Load multiple json files different folders - Very Urgent

[Anonymous]

Hi Team, I'm relatively new to Power BI and definitely require support to handle the situation of loading multiple json files from a folder. One of our job produces set of JSON files and places them under a folder. This job usually runs on demand. Everytime job runs it produces different number of JSON files however the structure of the JSON file will remain same. Everytime job is completed, we have requirement to extract certain data points from JSON files in a tabular form. Basically health of the each JSON output file is evaluated against few data points (e.g. Performance, SEO, accessibility, and etc ) within those json files.

Job Output

Variable - Number of json files

Static - Structure of json file and Expected insights (health card) for all those files

We would like to build a macro/function using Power BI for one instance of the output, save the macro, run the macro as and when the new set of json files become available.

Need help

Thanks

Pavan

[v-yuta-msft]

@Anonymous ,

---

We would like to build a macro/function using Power BI for one instance of the output, save the macro, run the macro as and when the new set of json files become available.

Not very clear about the requirement. Do you want to connect to the folder and run some script automatically using power bi? If it is, to be general, no built-in feature can do this. Maybe python script can achieve it.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF]

Fortunately, this is super easy in Power BI:

Get Data -> From Folder will return a table with all your JSON-files listed like so:

Then add a column with this formula: 

Json.Document( [Content] )

This will return the parsed JSON in a new column that you just expand using the arrow-signs.

If the first-level-element of your JSON is a list, the you will see it like so:

Choose "Expand to New Rows".

And if it is a record instead, you'll see a list of your record field alredy, under which you can directly start to select:

Everytime you refresh that query, all new files in the folder will automatically be included in those transformations as well. So no need for a macro here.