Power Query Unable to parse data from Blob storage

[PowerBIDev08]

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 second I see new folder gets created. If there is one transcation during that then one json file is created and if multiple transctions happens then still one file gets created with multiple json documents in one file (If I check the file in json validator it is inccorect and won't validate). 

Hence in Powerquery transformation it gives error for file with multiple json and works fine for single json object. 

with Multipe file: 

I have tried indidual json objects they are fine but I am not sure how to process each object seperately in PowerQuery. 

I would appriciate any guidance. Thanks!

[PowerBIDev08]

@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]

@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"

[Greg_Deckler]

@PowerBIDev08 Can you provide a sample of one of these single documents with multiple JSON documents in it?