Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

JSON - Remove Specific Row/Column

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!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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!

4 Replies

Replies have been turned off for this discussion
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    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
});