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.

  • gilderoylockhar's avatar
    gilderoylockhar
    Regular Visitor
    3 years ago

    Hi,

     

    Thank you for the response. I'm unable to change the format of the data source, as it is coming from a 3rd party API. I know how to replace ' with ", but I don't know how to add missing characters.

     

    Do I have any options?

     

    Thanks

  • gilderoylockhar's avatar
    gilderoylockhar
    Regular Visitor
    3 years ago

    I'd also add, that this is what I'm getting (after having replaced values) when I right-click on a cell and select "copy":

     

    [{"line_id": "22256", "ord": "1", "article_id": "238", "product_id": "240", "item_code": "20623PLWHI01", "item_title": "FLAT ANGLE 40X25MM MINI", "unit_id": "5", "unit": "EA", "group_id": "21", "group_code": "AG-00026", "group_title": "Lower Level - Moulding", "description": "10", "quantity": 1000, "shipped": 763, "part_status": "40", "item_price": 0.454, "item_price_cur": 0.454, "total_price": 454, "total_price_cur": 454, "discount_rate": None, "cost": 108.571228, "delivery_date": "1664924399", "actual_delivery_date": "1662717638", "profit": 345.428772, "part_status_txt": "Ready for shipment", "source": [{"lot_id": "32217", "lot_code": "L34306", "lot_status": "20", "lot_status_txt": "Received", "man_ord_id": "25149", "manufacturing_order_code": "MO25110", "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}, {"lot_id": "36751", "lot_code": "L39058", "lot_status": "20"...

     

    It looks like not all of the data is coming through...

    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Community Support
      3 years ago

      Hi gilderoylockhar ,

      As for why the JSON format is invalid, you can refer to the documents I provided in the previous reply, which has the same problem with you, it may help you.

      If you want to get the valid JSON file, you can use the JSON Formatter.

       

      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.