Easy parsing JSON from API

[gilderoylockhar]

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 proficiently.

I am making a call to a 3rd party API that is returning a JSON array as one of the columns in power query. The table in Power Query Editor is looking like:

The column "products" contains the JSON, a sample of which is here:

[{'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}]}]

I'm desperately trying to access/parse/expand this data so I can use it in my report. When I right-click on the column and choose Transform > JSON:

I receive errors in all the cells. When clicking on the error to find out more, I'm reading: 

DataFormat.Error: We found extra characters at the end of JSON input.
Details:
Value='
Position=2

Can someone help me? I've spent HOURS diving in an out of various posts on the subject, but haven't found a solution. I am NOT great at DAX, and I don't know many of the functions at all, but if properly explained, I might have a chance.

Thanks,

Guy

[v-yanjiang-msft]

Hi @gilderoylockhar ,

Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

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 | P...

"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]

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]

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.

[gilderoylockhar]

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