How to convert JSON response to tables in Power BI-CodePudding

I am the task of consuming from a webservice some data by means of REST, for the consumption of data from Power BI I have used as a source a blank query where later I have added in the advanced editor the following query:

let
    url = "http://*********",
    headers= [#"Content-Type"="application/json"],
    postData = Text.ToBinary("{""token"":""*************""}"),
    response = Web.Contents(
        url,
        [
            Headers = headers,
            Content = postData           
        ]
    ),
    jsonResponse = Json.Document(response)
in
    jsonResponse

As an answer I have clearly obtained a JSON file which is composed as follows:

{'result':'1',
'message':'Successful Operation',
'data':[
{'idActivity':'1001', 
'organization':'ABC-001' ,
'date':'6/10/2022 2:34:04 PM',
'lat':'57.3497300',
'lng':'-90.3929000',
'status':'0',
'company':'382',
'tag':'0'},
{'idActivity':'1002', 
'organization':'DEF-002',
'date':'6/10/2022 2:21:15 PM',
'lat':'83.6718200',
'lng':'-23.3464000',
'status':'0',
'company':'932',
'tag':'0'}]}

I would like to know if there is a way to convert this JSON file to tables and then be able to represent the information in power bi visuals?

CodePudding user response：

Click the parse JSON button. That should parse it automatically. You might need to replace your single quotes with double quotes ("). Your sample JSON worked for me using parse JSON when I did that.