Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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
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:
"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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |