Forum Discussion
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
- Import and convert this JSON strings to see how many hours our painters are working on each day.
- 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)
Two questions:
- 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?
- Are there any better ways to display the data, than in the 3 tables in the example?
Thanks!
David
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.
6 Replies
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.
Thanks guys - will check it out today and let you know if it works out :-)
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.
Try this tool: JSON validator. Hope this help!
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.
