JSON data in query

[V_Kyytsis]

Hello,

I'm having problems scripting my wayt to get to the data. There is a test IoT device in our office that outputs data here: 

http://duke-demo.events.10duke.com/get/db1650f0-bbac-11e5-83f0-a76492f3e0a6

The data has information about the sensor "sId" where 0x0060100 is recording temperature in "val" there is also timestamp "ts". There is another sensor 0x0060300 that is recording luminence in "val", with timestamp.

I'd like to get the data into Power BI so that I could visualise the timeseries of temperature. In future this will extend over several locations and sensors. 

Currently I get as far that I can see the JSON events (462 of them) and I can drill down to single event, however I'd need to extract the values from each sensor into a timeserie.. I'm sure it can be done, but the scripting logic is not something that I get.

Any ideas?

Thanks,

Ville

[ImkeF · Accepted solution]

This is the short version:

let
    Source = Json.Document(File.Contents("YourFilePath")),
    objects = Table.Combine(List.Transform(List.Range(Source[objects], 1, List.Count(Source[objects])-2), each Table.FromRecords(_[senses])))
in
    objects

But if you want to follow along the single transformation-steps, check out this version:

let
    Source = Json.Document(File.Contents("YourFilePath")),
    SkipFirstAndLastRecord = List.Range(Source[objects], 1, List.Count(Source[objects])-2),
    SelectSenses = List.Transform(SkipFirstAndLastRecord, each _[senses]),
    RecordsToTable = List.Transform(SelectSenses, each Table.FromRecords(_)),
    TableCombine = Table.Combine(RecordsToTable)
in
    TableCombine

[V_Kyytsis]

Thanks Imke!

So simple once you see it done. 

Best Regards,

Ville