Extracting nested list data from a REST API query

[PwrUser]

Hi all,

I am new to Power BI. I have a REST API query that returns Json data and some of the data is nested within a list. When I drill into the returned data, I can only see the first item of the list. How do I view/extract all items from the list ?

The returned data should look like this:

However, this is what I see when I drill down:

Top level:

2nd level:

3rd level:

4th level:

This is the query:

let
    Source = Json.Document(Web.Contents("https:<URL removed>")),
    result_set = Source[result_set],
    result_set1 = result_set{0},
    data = result_set1[data],
    data1 = data{0}
in
    data1

 As you can see, I can only see the first element of the list. I tried data{1} but I get the following error:

How do I access the rest of the list ?.

Thanks for any help/info.

[ZhangKun]

If the field_names field content (which corresponds to the table title) of each record in result_set is the same, you can try the following query:

let 
    // change to the previous code for getting json
    Source = ..., 
    TableList = List.Transform(
        Source[result_set], 
        each Table.FromRows([data], [field_names])
    )
in 
    TableList

[PwrUser]

Thanks @ZhangKun for the quick response. I get the data in tables now but still can only access the first element of the list.

This is what I get now:

The first table should have the following data:

Thanks.

[ZhangKun · Accepted solution]

Sorry, I forgot about Table.Combine. I tested it and it's ok, maybe you should check your data.

let 
    Source = Json.Document(File.Contents("C:\Users\Black\Desktop\data.json")), 
    tbl = 
    Table.Combine(
        List.Transform(
            Source[result_set], 
            each Table.FromRows([data], [field_names])
        )
    )
in 
    tbl

sample json 

{
"result_set": [
{
"field_names": ["a", "b"],
"data": [[1, 2], [3, 4], [5, 6]]
},
{
"field_names": ["a", "b"],
"data": [[11, 12], [13, 14], [15, 16]]
},
]
}

[PwrUser]

Hi @ZhangKun this is good!. It works if I save the Jason response to a file (e.g. run it via Postman, then save the response). However, if I call the API directly, I can only see the first two elements of each list. I'll work on this. Thnaks very much for the help.

[ZhangKun]

Some parameters of the web API may affect the return of results (and the order is not fixed), you can check that the parameters used in power query and postman are the same (pay special attention to headers, which are easier to ignore).

[PwrUser]

Thanks. Is it possible to check the full raw API response in Power BI ?

[ZhangKun]

yes, use Fiddler can do it.