Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
I would like to structure this into three tables in Excel / PowerBI
(see example below - i put them on one sheet here for an easy screenshot but would be separate tables in PowerBI)
Two questions:
Thanks!
David
Solved! Go to Solution.
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.
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.
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.
Thanks guys - will check it out today and let you know if it works out 🙂
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |