convert the json data to table in the Power BI Desktop

[rootchina]

Hello, 

Recently, I am trying analyze the data with json format. I am confused to convert the json to a table. Here's the json data https://data.cdc.gov/api/views/mr8w-325u/rows.json?accessType=DOWNLOAD 

1. How should I write the formula scripts in the advanced query editor? 

2. Is there any restricted condition with the data size when it's loaded into Power BI Desktop? 

3. Which solution will be faster in the refresh/schedule refresh, to store the json data into DocumentDB or Azure Blob?

Thanks,

Jared Zhang

[v-haibl-msft]

@rootchina

You can use the built-in json connector and use the convert into table and expand function in Query Editor.

There is no data size limitation for Power BI Desktop but there is limitation for the data published to Power BI Service. The refresh performance maybe also related to your data, you can have a try with these two conditions and compare the refresh time.

Best Regards,

Herbert

[rootchina]

Hi Herbert,

Thanks for your instruction.

I cleaned/tranformed the json and published the report to Power BI, however i encountered an error like "Please split your data into little dataset" when I try refreshing the data. The json data size only 70M is  stored in the Azure Blob.

So I would like to know if the phenomenon indicates that Power BI refresh does NOT suite the blob storage scenatio.

Thanks & Regards,

Jared Zhang

[v-haibl-msft]

@rootchina

The data size does not seems to be the cause since Power BI Service can refresh a dataset which is not beyond 1Gb.

How about the result if you store your json file locally instead of blob?

Best Regards,

Herbert