Change JSON Blob data during import

[Anonymous]

Hi,

I'm trying to import data from an Azure Blob Storage.

The files that are getting imported are JSON files, with one entry on each line.

They are lacking 2 things : 

• An opening "[" and a closing "]" for it to be interpreted as a table
• A comma "," at the end of each line

If I add those things (using search and replace in a text editor for example), the file becomes a valid .json file.

However, I have no idea how to do these operations in M on the "Combined Binaries" that I have imported. Using Replacer.ReplaceText throws an error (because my Binary files aren't text), and choosing to Transform the column type of my binary content to Text doesn't work.

If I try to use the Json.Document function as-is, it throws an error at the end of the first line, because it finds a "{" instead of a ",".

My json files are formatted as follows :

{"Event":"data1","Payload":{"Code":"data1","Context":"data1"}}

{"Event":"data2","Payload":{"Code":"data2","Context":"data2"}}

Is there a solution to my issue ?

[Anonymous · Accepted solution]

Hi again,

I've managed to display what I wanted ! The importation of data is really slow (it takes about 10 min to process the data for a week, which represents about 250k lines of data), but it works.

Here's how I did it, in case someone tries to do the same thing as I did :

let
Source = AzureStorage.Blobs("myazureblobsource"),
#"datastorage" = Source{[Name="datastorage"]}[Data],
#"Filtered Rows" = Table.SelectRows(#"atastorage", each [Date modified] > #datetime(2016, 4, 18, 12, 3, 22)),
#"Combined Binaries" = Binary.Combine(#"Filtered Rows"[Content]),
#"Combined Text" = Lines.FromBinary(#"Combined Binaries",1),
#"Json joined" = Replacer.ReplaceText(Lines.ToText(#"Combined Text"),"}{","}#(cr)#(lf){"),
#"Json Text" = Replacer.ReplaceText(#"Json joined","#(cr)#(lf){",",#(lf){"),
#"Json Text Complete" = Text.Combine({"[", #"Json Text", "]"}, ""),
#"Json list" = Json.Document(#"Json Text Complete"),
#"Data table" = Table.FromList(#"Json list", Splitter.SplitByNothing(), null, null, ExtraValues.Error),

Basically I had to transform the combined binaries into a list of Lines, then join the Lines into a Text (with Lines.ToText), then I replaced what I needed to be replaced in the text. Finally, I passed this text as an argument for Json.Document, split the result for it to be interpreted correctly by Power BI.

I did not include the last part of the process, which is the expansion of the JSON into a table, because the code was generated by Power BI when I clicked on the double arrows and it's really long.

Cheers 🙂

[rkanumuri]

Excellent.. that worked like a charm! This is a straightforward solution for anyone who is reading JSON data from Azure Storage account and trying to generate a report in the Power BI Desktop application.

[dkay84_PowerBI]

Could you help me implement your solution?  I have a blob storage account and I can connect to it and view the overview of the blobs within.  As you discussed, when I click the double arrow on the Content field (a column of binaries) it wont combine them as the JSON is not formatted correctly.

However, I'm not sure how to adapt your "datastorage" step for my needs.