Forum Discussion
Azure Stream Analytics and Power Bi
let Source = Json.Document(" { ""DevEUI_uplink"": { ""Time"": ""2024-01-26T04:05:19.131+00:00"", ""DevEUI"": ""C1CE8AFFF"", ""FPort"": 6, ""FCntUp"": 16976, ""LostUplinksAS"": 0, ""ADRbit"": 1, ""MType"": 4, ""FCntDn"": 16947, ""payload_hex"": ""65b32f7f101f8da78f00d2"", ""mic_hex"": ""08235228"", ""Lrcid"": ""00000233"", ""LrrRSSI"": -29.392658, ""LrrSNR"": 14, ""LrrESP"": -29.562201, ""SpFact"": 7, ""SubBand"": ""G0"", ""Channel"": ""LC4"", ""Lrrid"": ""10001EAE"", ""Late"": 0, ""Lrrs"": { ""Lrr"": [ { ""Lrrid"": ""10001EAE"", ""Chain"": 0, ""LrrRSSI"": -29.392658, ""LrrSNR"": 14, ""LrrESP"": -29.562201 } ] }, ""DevLrrCnt"": 1, ""CustomerID"": ""1100012387"", ""CustomerData"": { ""loc"": null, ""alr"": { ""pro"": ""LORA/Generic"", ""ver"": ""1"" }, ""tags"": [], ""doms"": [], ""name"": ""SHIP B17"" }, ""BaseStationData"": { ""doms"": [], ""name"": ""Milesight"" }, ""DriverCfg"": { ""mod"": { ""pId"": ""generic"", ""mId"": ""lora"", ""ver"": ""1"" }, ""app"": { ""pId"": ""Moko"", ""mId"": ""Testing"", ""ver"": ""1"" }, ""id"": ""custom:lw007-pir-sensor:1"" }, ""InstantPER"": 0, ""MeanPER"": 0, ""DevAddr"": ""0422FE94"", ""TxPower"": 4, ""NbTrans"": 1, ""Frequency"": 922.1, ""DynamicClass"": ""A"", ""payload"": { ""port"": 6, ""result"": 0, ""timestamp"": 1706241919, ""timezone"": ""UTC+08:00"", ""pir_state"": 0, ""door_state"": 1, ""temperature"": ""26.6"", ""humidity"": ""63.2"", ""temperature_change_state"": 3, ""humidity_change_state"": 3, ""low_battery_state"": 0, ""door_trigger_num"": 210 }, }, ""EventProcessedUtcTime"": ""2024-01-29T03:40:44.2998510Z"", ""PartitionId"": 0, ""EventEnqueuedUtcTime"": ""2024-01-29T03:37:56.3690000Z"", ""IoTHub"": { ""MessageId"": ""Tpx.ConnectionId.1879_0_259"", ""CorrelationId"": null, ""ConnectionDeviceId"": ""C1CE8AFFF"", ""ConnectionDeviceGenerationId"": ""638412529284229912"", ""EnqueuedTime"": ""2024-01-29T03:37:55.9360000Z"" } } "), payload = Source[DevEUI_uplink][payload] in payloadHow to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
This, however, gives you only the record you are after. It is not usable as such by Power BI Desktop. What I would do is to
parse that same JSON in Power Automate and then push the payload record into Streaming Hybrid Dataset (that I would have created before)pivot the record into a table. You may need to change the epoch timestamp into a UTC date first.... payload = Source[DevEUI_uplink][payload], #"Converted to Table" = Record.ToTable(payload), #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value"), #"Added Custom" = Table.AddColumn(#"Pivoted Column", "UTC Date", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[timestamp])) in #"Added Custom"Note that your timezone information is irrelevant for the sdata storage as the Power BI service runs on UTC. You can apply it to the UI if you want.
Looks like you are not parsing the JSON properly and instead attempt to treat it as a CSV. Any particular reason for that?
Could you provide more details on this? I'm having trouble understanding.
The data I passed is currently being processed by the LoRaWAN Network Server. I've established a connection in the LoRaWAN Network Server to connect with the Azure IoT Hub.
Subsequently, in Azure Stream Analytics, I've created an input to receive data from the IoT Hub and will then pass the data output to Power BI.You need to pass the data to Power BI as a JSON payload, not as CSV.
Thank you for your response. The data sent from the LNS comes in JSON format as shown below but it provides more details than necessary.
{
"DevEUI_uplink": {
"Time": "2024-01-26T04:05:19.131+00:00",
"DevEUI": "C1CE8AFFF",
"FPort": 6,
"FCntUp": 16976,
"LostUplinksAS": 0,
"ADRbit": 1,
"MType": 4,
"FCntDn": 16947,
"payload_hex": "65b32f7f101f8da78f00d2",
"mic_hex": "08235228",
"Lrcid": "00000233",
"LrrRSSI": -29.392658,
"LrrSNR": 14,
"LrrESP": -29.562201,
"SpFact": 7,
"SubBand": "G0",
"Channel": "LC4",
"Lrrid": "10001EAE",
"Late": 0,
"Lrrs": {
"Lrr": [
{
"Lrrid": "10001EAE",
"Chain": 0,
"LrrRSSI": -29.392658,
"LrrSNR": 14,
"LrrESP": -29.562201
}
]
},
"DevLrrCnt": 1,
"CustomerID": "1100012387",
"CustomerData": {
"loc": null,
"alr": {
"pro": "LORA/Generic",
"ver": "1"
},
"tags": [],
"doms": [],
"name": "SHIP B17"
},
"BaseStationData": {
"doms": [],
"name": "Milesight"
},
"DriverCfg": {
"mod": {
"pId": "generic",
"mId": "lora",
"ver": "1"
},
"app": {
"pId": "Moko",
"mId": "Testing",
"ver": "1"
},
"id": "custom:lw007-pir-sensor:1"
},
"InstantPER": 0,
"MeanPER": 0,
"DevAddr": "0422FE94",
"TxPower": 4,
"NbTrans": 1,
"Frequency": 922.1,
"DynamicClass": "A",
"payload": {
"port": 6,
"result": 0,
"timestamp": 1706241919,
"timezone": "UTC+08:00",
"pir_state": 0,
"door_state": 1,
"temperature": "26.6",
"humidity": "63.2",
"temperature_change_state": 3,
"humidity_change_state": 3,
"low_battery_state": 0,
"door_trigger_num": 210
},
},
"EventProcessedUtcTime": "2024-01-29T03:40:44.2998510Z",
"PartitionId": 0,
"EventEnqueuedUtcTime": "2024-01-29T03:37:56.3690000Z",
"IoTHub": {
"MessageId": "Tpx.ConnectionId.1879_0_259",
"CorrelationId": null,
"ConnectionDeviceId": "C1CE8AFFF",
"ConnectionDeviceGenerationId": "638412529284229912",
"EnqueuedTime": "2024-01-29T03:37:55.9360000Z"
}
},
The data I need is only.
"payload": {
"port": 5,
"result": 0,
"timestamp": 1706499727,
"timezone": "UTC+08:00",
"pir_state": 1,
"door_state": 1,
"temperature": "29.0",
"humidity": "56.7",
"temperature_change_state": 3,
"humidity_change_state": 3,
"low_battery_state": 0,
"door_trigger_num": 210},
Can you guide me on how to obtain only the specific data I need? Thank you in advance.
