Forum Discussion

PowerBIDev08's avatar
PowerBIDev08
Frequent Visitor
3 years ago

Power Query Unable to parse data from Blob storage

Hello,   I am facing an issue and got bit blocked. Here is the scenario:   I am creating a Power BI dashboard for the data coming from IOT Central via Blob Storage. In blob storage for every seco...
Need Help
Tips and Tricks
PowerBIDev08's avatar
PowerBIDev08
Frequent Visitor
3 years ago

Greg_Deckler  Here is the data

{"applicationId":"574a7a52-2445-4b53-a208-b09ef812a910","deviceId":"Device-01","enqueuedTime":"2023-06-20T11:59:07.319Z","enrichments":{},"messageProperties":{"$.cdid":"Device-01"},"messageSource":"telemetry","schema":"default@v1","telemetry":{"SuccessfulworkEvent":{"date":"06/14/23","workCompletionTime":"2023-06-14T17:21:50.547Z","workStartTime":"2023-06-14T17:20:19.935Z","workTemp":63,"salesPerson":"","hoursRemaining":29,"capacityUsed":15,"type":"SUCCESS"}},"templateId":"dtmi:modelDefinition:c2test:obtest2n"}
{"applicationId":"574a7a52-2445-4b53-a208-b09ef812a910","deviceId":"Device-01","enqueuedTime":"2023-06-20T11:59:07.366Z","enrichments":{},"messageProperties":{"$.cdid":"Device-01"},"messageSource":"telemetry","schema":"default@v1","telemetry":{"SuccessfulworkEvent":{"date":"06/14/23","workCompletionTime":"2023-06-14T17:21:50.575Z","workStartTime":"2023-06-14T17:20:19.935Z","workTemp":63,"salesPerson":"","hoursRemaining":29,"capacityUsed":15,"type":"SUCCESS"}},"templateId":"dtmi:modelDefinition:c2test:obtest2n"}
{"applicationId":"574a7a52-2445-4b53-a208-b09ef812a910","deviceId":"Device-01","enqueuedTime":"2023-06-20T11:59:07.366Z","enrichments":{},"messageProperties":{"$.cdid":"Device-01"},"messageSource":"telemetry","schema":"default@v1","telemetry":{"SuccessfulworkEvent":{"date":"06/14/23","workCompletionTime":"2023-06-14T17:21:51.578Z","workStartTime":"2023-06-14T17:20:19.935Z","workTemp":63,"salesPerson":"","hoursRemaining":29,"capacityUsed":15,"type":"SUCCESS"}},"templateId":"dtmi:modelDefinition:c2test:obtest2n"}
{"applicationId":"574a7a52-2445-4b53-a208-b09ef812a910","deviceId":"Device-01","enqueuedTime":"2023-06-20T11:59:07.384Z","enrichments":{},"messageProperties":{"$.cdid":"Device-01"},"messageSource":"telemetry","schema":"default@v1","telemetry":{"SuccessfulworkEvent":{"date":"06/14/23","workCompletionTime":"2023-06-14T17:21:52.582Z","workStartTime":"2023-06-14T17:20:19.935Z","workTemp":63,"salesPerson":"","hoursRemaining":29,"capacityUsed":15,"type":"SUCCESS"}},"templateId":"dtmi:modelDefinition:c2test:obtest2n"}
{"applicationId":"574a7a52-2445-4b53-a208-b09ef812a910","deviceId":"Device-01","enqueuedTime":"2023-06-20T11:59:07.397Z","enrichments":{},"messageProperties":{"$.cdid":"Device-01"},"messageSource":"telemetry","schema":"default@v1","telemetry":{"SuccessfulworkEvent":{"date":"06/14/23","workCompletionTime":"2023-06-14T17:21:53.609Z","workStartTime":"2023-06-14T17:20:19.935Z","workTemp":63,"salesPerson":"","hoursRemaining":29,"capacityUsed":15,"type":"SUCCESS"}},"templateId":"dtmi:modelDefinition:c2test:obtest2n"}


Thank you for looking into this.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

PowerBIDev08 I'm assuming that you are trying to connect via something like AzureStorage.Blobs or AzureStorage.BlobContents. You can actually transform that file, but not parsing via JSON. You should be able to do something like this (note that you should be able to replace File.Contents with something like AzureStorage.BlobContents:

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\gdeck\OneDrive\Books\The Definitive Guide to Power Query\Chapter 7\sample.json"), null, null)}),
    #"Transformed Column" = Table.TransformColumns(Source, {"Column1", Json.Document}),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Transformed Column", "Column1", {"applicationId", "deviceId", "enqueuedTime", "enrichments", "messageProperties", "messageSource", "schema", "telemetry", "templateId"}, {"applicationId", "deviceId", "enqueuedTime", "enrichments", "messageProperties", "messageSource", "schema", "telemetry", "templateId"}),
    #"Expanded enrichments" = Table.ExpandRecordColumn(#"Expanded Column1", "enrichments", {}, {}),
    #"Expanded messageProperties" = Table.ExpandRecordColumn(#"Expanded enrichments", "messageProperties", {"$.cdid"}, {"messageProperties.$.cdid"}),
    #"Expanded telemetry" = Table.ExpandRecordColumn(#"Expanded messageProperties", "telemetry", {"SuccessfulworkEvent"}, {"telemetry.SuccessfulworkEvent"}),
    #"Expanded telemetry.SuccessfulworkEvent" = Table.ExpandRecordColumn(#"Expanded telemetry", "telemetry.SuccessfulworkEvent", {"date", "workCompletionTime", "workStartTime", "workTemp", "salesPerson", "hoursRemaining", "capacityUsed", "type"}, {"telemetry.SuccessfulworkEvent.date", "telemetry.SuccessfulworkEvent.workCompletionTime", "telemetry.SuccessfulworkEvent.workStartTime", "telemetry.SuccessfulworkEvent.workTemp", "telemetry.SuccessfulworkEvent.salesPerson", "telemetry.SuccessfulworkEvent.hoursRemaining", "telemetry.SuccessfulworkEvent.capacityUsed", "telemetry.SuccessfulworkEvent.type"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded telemetry.SuccessfulworkEvent",{{"applicationId", type text}, {"deviceId", type text}, {"enqueuedTime", type datetime}, {"messageProperties.$.cdid", type text}, {"messageSource", type text}, {"schema", type text}, {"telemetry.SuccessfulworkEvent.date", type date}, {"telemetry.SuccessfulworkEvent.workCompletionTime", type datetime}, {"telemetry.SuccessfulworkEvent.workStartTime", type datetime}, {"telemetry.SuccessfulworkEvent.workTemp", Int64.Type}, {"telemetry.SuccessfulworkEvent.salesPerson", type any}, {"telemetry.SuccessfulworkEvent.hoursRemaining", Int64.Type}, {"telemetry.SuccessfulworkEvent.capacityUsed", Int64.Type}, {"telemetry.SuccessfulworkEvent.type", type text}, {"templateId", type text}})
in
    #"Changed Type"