Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

JSON issue with data lake

  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 data...
data engineering
Data Pipeline
Lakehouse
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous,

    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.

ADugh's avatar
ADugh
New Member
11 months ago

Hi, This is an old post however I wandered around looking for a working solutiom as I had the same problem until I found the code to solve reading NDJSON files. Here is the ChatGPT version which is working for me. Thanks.

let
// 0) Start from your table with [Content] as TEXT (Already have Text.FromBinary)
Source = SourceTable,

// 1) Normalise line endings & strip BOM, then split into lines
Norm = Table.TransformColumns(
Source,
{{"Content",
each
let
t1 = Text.Replace(_, "#(cr,lf)", "#(lf)"),
t2 = Text.Replace(t1, "#(cr)", "#(lf)"),
bom = Character.FromNumber(65279),
t3 = Text.Replace(t2, bom, "")
in t3,
type text}}
),
AddLines = Table.AddColumn(Norm, "Line", each Text.Split([Content], "#(lf)")),
Explode = Table.ExpandListColumn(AddLines, "Line"),

// 2) Trim & drop blanks
Clean = Table.SelectRows(
Table.TransformColumns(Explode, {{"Line", each Text.Trim(_), type text}}),
each [Line] <> ""
),

// 3) Parse each NDJSON line
Parsed = Table.AddColumn(Clean, "json", each try Json.Document([Line]) otherwise null, type any),
KeepGood = Table.SelectRows(Parsed, each [json] <> null),

// 4) Expand top-level fields (union all field names so schema drift doesn't break refresh)
FieldNames = List.Sort(List.Union(List.Transform(KeepGood[json], each try Record.FieldNames(_) otherwise {}))),
Expanded = if List.Count(FieldNames) > 0
then Table.ExpandRecordColumn(KeepGood, "json", FieldNames, FieldNames)
else KeepGood,

// 5) (optional) drop helpers
Result = Table.RemoveColumns(Expanded, {"Content"})
in
Result