cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Load multiple json files different folders - Very Urgent

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

2 REPLIES 2
Highlighted
Super User III
Super User III

Re: Load multiple json files different folders - Very Urgent

Fortunately, this is super easy in Power BI:

 

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

 

image.png

 

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:

 

image.png

 

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:

 

image.png

 

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Community Support
Community Support

Re: Load multiple json files different folders - Very Urgent

@sreemojura ,

 


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.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors