JSON issue with data lake

[AaronK11]

 

I am currently using a pipeline to connect and bring in all of the tables i want from dataverse into my datalake. I use the method of copying from the data assistant, then connecting to dataverse, using the environment domain and then connecting. Once connected i choose tables and select all of the tables i want. When running my pipline to bring move my tables from dataverse to my lakehouse I use the json formatting as I've had issue with expanding columns (the don't appear) in parquet format. After the file appears in my folder inside the lakehouse I move onto the gen2 dataflow. From here I use th lakehouse connector and try to bring the json file into my report. However when trying to read the JSON file. I get the error we found extra characters at the end of the json output. This file has around 7000 rows. And I haven't modified it in any way I've brought it straight from run pipeline and dataverse. So I don't understand what the issue is. I tried to do it this way as parquet format missed out data in certain columns and didn't give me an option to expand columns I can expand in power bi. Any help would be fab.

[v-mdharahman · Accepted solution]

Hi @AaronK11,

Thanks for reaching out to the Microsoft fabric community forum.

The JSON format from a Copy Activity in Data Factory or Fabric Pipelines is not always a standard JSON array. Instead, it might output newline-delimited JSON (NDJSON), where each line is a separate JSON object.

Ex:

{"id":1,"name":"Alice"}
{"id":2,"name":"Bob"}
{"id":3,"name":"Charlie"}

This is not valid as a JSON array (which looks like [{}, {}, {}]), and Power Query  which is used in Gen2 Dataflows expects valid JSON, not NDJSON. Maybe that why, when Power BI or Dataflow tries to read this as a single JSON file, it fails with the error "We found extra characters at the end of the JSON output", as it tries to parse the first line as a complete JSON, then hits the second object unexpectedly. If you want to use JSON, you need to convert the NDJSON into a proper JSON array before loading it into the Dataflow.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @AaronK11,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[v-mdharahman]

Hi @AaronK11,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.