Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

Need Help

2 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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.

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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.