Azure Stream Analytics and Power Bi

[ASY]

Hello all,

I am currently attempting to connect my LoRa Network Server (LNS) with Power BI using Microsoft Azure.

LNS --> Azure IoT Hub --> Azure Stream Analytics --> Power BI workspace

I can confirm that the device data is successfully sent from LNS to Azure IoT Hub. However, when creating a dataset in Power BI, the data appears in a format that is not suitable for generating reports and dashboards.

From the Azure Stream Analytics query, I can observe something like this.

Does anyone know how I can customize the query to transmit the data (sensor temperature, humidity) contained within the DevEUI_uplink column? Alternatively, is it possible to customize the data received from Azure Stream in Power BI? or is there other alternative for me to connect my LNS with Power BI?

Thank you in advance 🙂

[ASY]

Got it. Thank you so much for your help

[ASY]

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 that 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.

[lbendlin]

Looks like you are not parsing the JSON properly and instead attempt to treat it as a CSV.  Any particular reason for that?

[ASY]

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.

[lbendlin]

You need to pass the data to Power BI as a JSON payload, not as CSV.

[ASY]

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.

[lbendlin · Accepted solution]

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
    payload

How 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.