error read Json file with power bi that are automatically hosted in my blob storage

[carlacl9]

Hello, I have the following problem, I have an adf that every time a pipeline is executed the information is automatically saved in my Azure Blob Storage container that uses it as a data source for power bi, the information of the executed pipelines is saved in JSON format, when I try to get the data in power bi to build my dashboard I get the following error:

one of the options i tried was to transform it into text and then into json, but i keep getting this message:

I hope you can help me.

Thank you

[carlacl9]

one of the options i tried was to transform it into text and then into json, but i keep getting this message:

I hope you can help me.

Thank you

[Jimmy801 · Accepted solution]

Hello @carlacl9 

from the error message it seems that there is something wrong with the JSON-file. (some additional characters at the end - json should always end with a }). To be precise it should be the first file of your folder table. This is where power query identifies the function to be applied. If the first would be fine and the second wrong, then the error should show up on the subsequent steps.

So fix your data sources beforehand. If you are not able to change them, you have to transform them, reading from Binary with Text.FromBinary and then fix the error and transforming to binary and json afterwards.

Here an example. It cuts everything after the last }

let
    Source = Folder.Files("C:\Users\ltnhoe\Documents"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".json")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each  Text.Start(Text.FromBinary(_[Content]), Text.PositionOf(Text.FromBinary(_[Content]), "}")+1) ),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.ToBinary([Custom]), type binary),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Json.Document([Custom.1]))
in
    #"Added Custom2"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy