Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Change JSON Blob data during import

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 ?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 🙂

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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 🙂

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors