Forum Discussion

david2's avatar
david2
Helper I
10 years ago
Solved

Parsing JSON data into PowerBI table

Hello PowerBI team/community,   I am new to PowerBI and setting up preliminary dashboards for our company. We're building a tech-driven large scale painting company. We use Pipedrive (CRM SaaS) to ...
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    david2

    You didn't post the whole JSON. According to the description, suppose that you have a json file as below,

     

     

    [
   {
      "project id":"123",
      "value":"2000",
      "trans":[
         {
            "date":"2016-07-31T22:00:00.000Z",
            "startTime":"07:00",
            "endTime":"16:00",
            "painters":[
               "John",
               "Pete"
            ]
         },
         {
            "date":"2016-08-01T22:00:00.000Z",
            "startTime":"07:00",
            "endTime":"16:00",
            "painters":[
               "John",
               "Pete"
            ]
         },
         {
            "date":"2016-08-02T22:00:00.000Z",
            "startTime":"07:00",
            "endTime":"16:00",
            "painters":[
               "John",
               "Pete"
            ]
         }
      ]
   }
]

     

    to get an expected output, you can reference the pbix attached.

     

    To apply the demo to your case, replace the embeded JSON with file.content("yourJSONpath") in advanced editor.

     

ankitpatira's avatar
ankitpatira
Community Champion
10 years ago

david2

 

1. power bi has default JSON document connector you can use that to import your saved JSON strings.

 

2. once imported you get access to query editor where you can perform number of data manipulation tasks and use it. depending on your end goal you can use number of visuals and not just the table visuals such as column and bar charts which are standard examples.