Forum Discussion

gilderoylockhar's avatar
gilderoylockhar
Regular Visitor
3 years ago

Easy parsing JSON from API

Hi,   This is my first time posting to this forum. I'm not an experienced DAX user, so solutions that contain paragraphs of code are not what I'm hoping to be given. I can, however, use PowerBI pro...
Need Help
v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

Hi gilderoylockhar ,

The error is because the JSON you provided is not valid, after verify it in the JSON Formatter, found there're incorrect and missing quotes. Modify it to:

[
   {
      "line_id":"15625",
      "ord":"1",
      "article_id":"5269",
      "product_id":"5271",
      "item_code":"40150PL",
      "item_title":"CABLE RESTRAINT",
      "unit_id":"5",
      "unit":"EA",
      "group_id":"11",
      "group_code":"AG-00014",
      "group_title":"Finished Goods - Moulding",
      "description":"None",
      "quantity":15935,
      "shipped":15935,
      "part_status":"50",
      "item_price":0.039,
      "item_price_cur":0.039,
      "total_price":621.47,
      "total_price_cur":621.47,
      "discount_rate":"None",
      "cost":179.303807,
      "delivery_date":"1630623599",
      "actual_delivery_date":"1630561547",
      "profit":442.166193,
      "part_status_txt":"Delivered",
      "source":[
         {
            "lot_id":"24563",
            "lot_code":"L25901",
            "lot_status":"20",
            "lot_status_txt":"Received",
            "man_ord_id":"19339",
            "manufacturing_order_code":"MO19310",
            "manufacturing_order_status":"40",
            "manufacturing_order_status_txt":"Done",
            "pur_ord_id":"None",
            "purchase_order_code":"None",
            "purchase_order_status":"None",
            "purchase_order_status_txt":"None"
         }
      ]
   }
]

Then after transform, error disappear.

Here's the whole M syntax, you can copy-paste to a blank query to see the details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVNdb9sgFP0rlp/XCIxx5r6lW1ZVSrMp61scWRbQBskFF8O0atp/H7YbDA2tVj9Y+J5zrg/3Y79P95VIkuTP+LZPlbZcsJrTKr2sUoiLDFfppxmV6gUJoo3SnLROhrOiDPBOSWqInvFlqOeaPdZEUjbCOYAY/NicMzTX7UT5srrarJPd+ufdbnWzvQuoRvD5T2fIGF6vgviDkqZzd4YRzHlbXV8AAGAe4czuvnHB+yOjybWUtE8ukltpWsrFQ6CirCeKd5pLMaq2UrCA8GQaYZM+WxTiEmEP6o+86xiNIJ3tRd3rRpt+qgA4r2OnOBmcggVAZRSsiVERgpa6aZ28yOAiX8bhlwRnFMp7Io3QtWo0i1+byH7oElyWCwTQZxDIWct/MfVc05McFggUGcJlOHEN0cZ6ifNxAXG+fD2h9+Nw5Hm2gEUBSxQvaq1/TzP0dUo9NMFvjDRqLM7+FPO3y+2YdBOa5bhAXgqf4qZuk+ESwDdYXrMz8C7Hed8xwqx5GmE/NqK2S+62oUSojNPM/VBkZcd6EDA12739bmUw5iUm8/znH9PMvQhnyLXNKP8u2zdZ5Nj07PUt/o/uuf+IwFmfRJ7m7+l8GA/285AeDv8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{},Json.Document)
in
    #"Parsed JSON"

Here's similar threads for your reference: 

DataFormat.Error: We found extra characters at the end of JSON input. Details: Value= Position=0 | Power Query

"We found extra characters at the end of JSON input" error. | MrExcel Message Board

JSON format error · Issue #7 · stansw/vsts-open-in-powerbi · GitHub

We Found Extra Characters at End of JSON Line - Microsoft Community Hub

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.