Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
david2
Helper I
Helper I

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 keep track of the paint projects we carry out, and schedule painters on these projects using a self-built tool. The scheduling data is saved in a JSON string that is saved for each project, which each has unique ID.

 

Let's assume for paint project 123 that the JSON string looks like this:

 

[{"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"]}]

 

(hard enters added for readability)

 

 

I would like to do the following 2 things in PowerBI

 

  1. Import and convert this JSON strings to see how many hours our painters are working on each day.
  2. Use these hours to allocate the revenue made with these paint projects to days, based on the number of hours worked on each day.

 

I would like to structure this into three tables in Excel / PowerBI 

  • Table 1 would break down the JSON string into one line per unique combination of Project ID / Painter / Day (Column A-F in pic below)
  • Table 2 would represent the revenue per project ID (this one is easy 🙂 ) (Column H-I)
  • Table 3 would break down the revenue from Table 2 prorated split by the number of hours from table 1 (Column K-N)

(see example below - i put them on one sheet here for an easy screenshot but would be separate tables in PowerBI)

 

Example

 

Two questions:

 

  1. How can i convert the JSON string into PowerBI-readable data, like column A-F? Can i do this inside PowerBI (ideally), or do i have to write a script that converts the data before importing it into PowerBI?

  2. Are there any better ways to display the data, than in the 3 tables in the example?

 

Thanks!

 

David

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee

@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.

Capture.PNG

 

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

Capture.PNG

 

View solution in original post

6 REPLIES 6
jameson
New Member

Try this tool: JSON validator. Hope this help!

Eric_Zhang
Employee
Employee

@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.

Capture.PNG

 

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

Capture.PNG

 

Hello, how can i split the result into 3 diferent tables? I need for it, but i don't know the best way for it.


@nsimonov wrote:

Hello, how can i split the result into 3 diferent tables? I need for it, but i don't know the best way for it.


@nsimonov

Since this thread is old and already closed. For your question, could you post a new thread? Thanks for your understanding. 🙂

Thanks guys - will check it out today and let you know if it works out 🙂

ankitpatira
Community Champion
Community Champion

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.