Combining JSON files in a Sharepoint Folder that are new line delimited

[Mike282]

So I'm trying to combine multiple amounts of JSON files that I've extracted from an Azure Data Lake.

I realised that even though they're in a JSON format they're in a new line delimited format. Meaning it's lacking the opening array brackets [] and each JSON object is separated by a new line instead of a comma.

E.g.

{"name":"test1"}

{"name":"test2"}

When I use to combine files from a Sharepoint folder for example I would normally first:

1. Add in the data source Sharepoint Folder

2. Put in the URL of the folder

3. Filter by Folder Path to all the files (as they're ordered in specific folders)

4. Select Binary and click on the Combine Files feature

5. It creates all the Transform file sample file and function to combine the various JSON files but I'm getting these errors

6. Tracing back a step I can see that the json files that have more than 1 line which are all the ones where the new line delimited JSON has more than 1 record is throwing errors

7. Expanding the error you can see it's formatting issues that arise from the new line delimited formatting of the json files.

I don't know how to resolve this issue. I tried one of the methods like the below:

https://community.fabric.microsoft.com/t5/Power-Query/Change-JSON-Blob-data-during-import/td-p/32121

But I received an error that I ran out of memory.

Is there anyway around it? For context the json files are from Dynamics 365 Customer Insights Journey email statistics. I've noticed the data lake json files all contain new line delimited json and it doesn't seem to work with Power BI.

[OwenAuger · Accepted solution]

Hi @Mike282 

One thing you could try (similar to the post you linked above) is assume that any errors in handling the JSON files are due to the line-delimited format, and use try/otherwise to handle appropriately.

Specifically, in the query Transform Sample File, locate the first step ("Source") which should look something like:

 = Json.Document(Parameter1)

and change it to:

= try Json.Document(Parameter1) otherwise Json.Document(Text.ToBinary("["&Text.Combine(Lines.FromBinary(Parameter1),",")&"]"))

If Json.Document applied to the original file returns an error, this instead adds the square brackets and commas, and converts back to binary before applying Json.Document.

Does this help at all in your case?

Longer-term, I would suggest something a bit more robust, such as a function that detects the type of JSON file before attempting to apply Json.Document, and applies appropriate transformations first.

[Mike282]

Thanks OwenAuger!

Really appreciate your help. I tried modifying the step as per below:

Now I'm getting the below error:

[OwenAuger]

Thanks for trying that @Mike282 🙂
It appears that the steps after the Source step in Transform Sample File don't work as intended when applied to a line-delimited JSON with the square brackets and comma-delimiters added.

It looks like you will actually need to modify Transform Sample File so that it applies different transformations depending on the file type.

To figure this out, probably the best way would be to copy one or more of the line-delimited files to a separate folder, and try using that as the source folder.

Then modify the steps of Transform Sample File so that it produces the correct output format.

After that, I think we have to go with the more "robust" approach where we detect the type of file and apply the corresponding transformations.

Could you possibly provide examples of the two types of JSON files (at least the structure), assuming they are more complex than the sample in the first post.

Regards

[Mike282]

Hi Owen!!

All good. Thank you for all your help. I found out what the issue is and updated the query. It appears the files seem to have a mixture of list and records so combining them was throwing errors as you get a mix of the two. 

So adding some conditions based on whether it's a record or a list solved the issue!

Thank you again for your help. I'll mark  your post as the solution as I couldn't of done it without you!!