cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
olimilo
Post Patron
Post Patron

Streaming Dataset from a JSON API

Hi everyone,

So we are pulling data from a websource using a JSON API. However, the data that we get from the JSON API is not laid out in a flat file-like structure but has columns that look like the one below:

 

data
{List}
1/1/2020

Passed

 

So basically, I have to transform the data first to get it to something more usable. My question is, the PBI streaming dataset looks like it requires to have your file in a flat file-like structure. Since my data isn't flat file-like in the first place, how can I achieve a streaming dataset with my data?

3 REPLIES 3
amitchandak
Super User
Super User
Greg_Deckler
Super User
Super User

@olimilo - Use Azure Streaming Analytics to transform your data prior to getting to Power BI.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler @amitchandak 

I tried refreshing the data using the dataset tab in the PBI service and got this error: Query contains unsupported function. Function name: Web.Contents.

 

Which is funny because when I refreshed the data in PBI desktop, the refresh went without a hitch. Will this have any bearing on us trying to achieve a realtime connection to our data? This is the connector that I used:

 

 

 

= Json.Document(Web.Contents(JSONSourceURL, [Timeout=#duration(0, 0, 30, 0), Headers=[#"User-Agent"="Power BI", Accept="application/jsonapi", #"Content-Type"="application/json", Authorization="..."]]))

 

 

Edit: I saw on the reference page below that JSON data sources does not support DirectQuery/Live connection - does that mean that we're out of options in this case? Or is it not possible for us to schedule data refreshes?

 

https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors