Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks Imke!
So simple once you see it done.
Best Regards,
Ville
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |