Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
gilderoylockhar
Regular Visitor

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 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:

 

Screenshot 2022-11-09 at 16.15.24.pngThe 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:

 

Screenshot 2022-11-09 at 16.18.42.png

 

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

5 REPLIES 5
v-yanjiang-msft
Community Support
Community Support

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
Community Support
Community Support

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.

vkalyjmsft_0-1668067440125.png

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.

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

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.

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.