cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dieptruong11 Occasional Visitor
Occasional Visitor

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 Regular Visitor
Regular Visitor

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

 

 

 

Time Visitor
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
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