Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dieptruong11
New Member

Export data in JSON format

How can I export a table into JSON format? 

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@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
Anonymous
Not applicable

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

 

 

 

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.

v-sihou-msft
Employee
Employee

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.