cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dieptruong11
New Member

Export data in JSON format

How can I export a table into JSON format? 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Export data in JSON format

@dieptruong11

 

In Power BI Desktop, you can use the R script to convert the data frame into JSON format and export into a JSON file.

 

1. Configure your R environment. Install readr and jsonlite packages.

 

2. Drag the fields you want to export into a R visual. Write R script below and Run:

 

library(readr)
library(jsonlite)
x<-toJSON(dataset)
cat(x)
write_lines(x,path = "/Program Files/R/R-3.3.3/write_json.json")
plot(dataset)

 

 

Capture.PNG

 

Ps: The file need to be created first with permission granted to packages.

 

Capture1.PNG

 

You can see the generated JSON format data in R studio.

 

55.PNG

 

56.PNG

 

Regards,

View solution in original post

3 REPLIES 3
Moderator v-sihou-msft
Moderator

Re: Export data in JSON format

@dieptruong11

 

In Power BI Desktop, you can use the R script to convert the data frame into JSON format and export into a JSON file.

 

1. Configure your R environment. Install readr and jsonlite packages.

 

2. Drag the fields you want to export into a R visual. Write R script below and Run:

 

library(readr)
library(jsonlite)
x<-toJSON(dataset)
cat(x)
write_lines(x,path = "/Program Files/R/R-3.3.3/write_json.json")
plot(dataset)

 

 

Capture.PNG

 

Ps: The file need to be created first with permission granted to packages.

 

Capture1.PNG

 

You can see the generated JSON format data in R studio.

 

55.PNG

 

56.PNG

 

Regards,

View solution in original post

Alonam Helper I
Helper I

Re: Export data in JSON format

can this approached to be used to store historical data from live data streams such as New Relic?

 

I get live data form NR for past 14 days; we cannot get any longer duration and wont be able to do it in the near future.

However, my client wants more data to be stored in PBI for historical analysis.

They proposed to get data every day (to avoid loading issues) from NR, save it locally and refresh charts daily.

We do not have any automation here except that NR refreshes. Manual refresh of reports and dashboards is not gonna work in long term either.

Any ideas on how to implement the solution if any?   @v-sihou-msft 

Thank you,

Alona

 

 

 

Highlighted
Time
Regular Visitor

Re: Export data in JSON format

This is what we do - we have a snapshot of data that changes every 15 minutes but need to audit specific times later on, so on each query run it ends in a rscript that writes to a .txt in our case (will be changing to json).  We can then load that and have a full historical as long as no refreshes missed.

 

We have it set up to scheduled refresh so the whole thing is automatic.  If you turn off "use miltiple processors" you can even control the order the data refreshes in the same report - we have the current run data included in our historical file before pbi loads the historical file on the same refresh.

 

The historical file is managed/connected through a gateway which has all the r installed.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors