cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
david2 Regular Visitor
Regular Visitor

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

Accepted Solutions
Microsoft Eric_Zhang
Microsoft

Re: Parsing JSON data into PowerBI table

@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
ankitpatira Super Contributor
Super Contributor

Re: Parsing JSON data into PowerBI table

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

Microsoft Eric_Zhang
Microsoft

Re: Parsing JSON data into PowerBI table

@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

david2 Regular Visitor
Regular Visitor

Re: Parsing JSON data into PowerBI table

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

nsimonov Frequent Visitor
Frequent Visitor

Re: Parsing JSON data into PowerBI table

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.

Microsoft Eric_Zhang
Microsoft

Re: Parsing JSON data into PowerBI table


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

jameson Frequent Visitor
Frequent Visitor

Re: Parsing JSON data into PowerBI table

Try this tool: JSON validator. Hope this help!

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors