JSON - Remove Specific Row/Column

[Anonymous]

Dear Power BI Community,

I was hoping to see if anyone had worked around this issue before. I am making an API call through Python to get data from a service that is in the JSON format.

I am having trouble with a specific column called "customer_notes" - it can cause the parse to error out sometimes.

Is there any way to remove the "customer_notes": "(whatever the customer types in)", before I use the power query parser?

Basically need a way to say remove "customer_notes": to the next comma

Thank you!

[Anonymous]

table.deleteRow(15)
.then(function(){
    //Call your api to delete from JSON file 
})
.catch(function(error){
    //handle error deleting row
});row.delete()
.then(function(){
    //run code after row has been deleted
})
.catch(function(error){
    //handle error deleting row
});

[Anonymous · Accepted solution]

@Anonymous  not sure if this answers your question as I might need more context. Are you just trying to parse the JSOn object and only return the elements except for "customer notes". If so I recommend trying the json-normalize library. Try "from pandas.io.json import json_normalize" then "json_normalize(#API Response JSON Object#)" to convert the json object into a DF you want. From there simply parse the DF and do not extract the values of "customer notes" and feed this data into a new data structure that you can feed into Power BI via CSV or however else you are doing it. Apologies if I totally misunderstood the context or what you were asking for. Best!

[Anonymous]

Yes exactly, I am trying to return all the elements except customer notes!

Let me look into that getting this all to even work was a whirlwind so I appreciate the help.

[Anonymous]

@Anonymous no worries. let me know if you need further help with normalizing the JSON or parsing the DF you create. I can possibly send over snippets of the Python code I used to pull data from the Google Maps Distance matrix API if it would help you. Best!