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