How to convert JSON response to tables

[sebastianslz]

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? 

[Anonymous · Accepted solution]

Hi @sebastianslz ,

I think you may refer to blogs about how to convert json to table in Power BI Desktop to learn more details.

For reference:

Convert Data from Power BI JSON to Table in 10 Easy Steps

Specifying JSON Query in Power Query – Example Statistics Sweden

Loading a json file into Power Query

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.