Web connection pulls all data but every record is nested rather than placed in a flat table

[Anonymous]

When calling the Pipedrive API using a Web connection, I get nested results in the Query Editor rather than a table of data. For example, I receive the following headers; data, additional_data, and related_objects.  I can click on the results and drill down to individual records, but I was expecting a single table with all of the results. I’ve read that a REST API can require additional calls to pull all of the data, but this call is already providing all of the data. It’s just buried/nested.  Here’s the query/URL I’m using.

= Json.Document(Web.Contents("https://api.pipedrive.com/v1/deals?filter_id=AAA&api_token=BBBBBBBBBBBBBBBBBBBBBBBBB"))

[ImkeF]

Please paste pic of how the records are nested. (Can give solution just based on that)

[Anonymous]

Starting from the left, that's what I first get in the Query Editor when I first run the query.  If I click on 'data/List', I get the next image (which is a set of individual records).  If I click on one of the 'records', I get the details for that specific record.  Any thoughts?

[ImkeF]

In the second pic where you have a list of records: Transform it to a table and then you can expand the records as if they were table columns:

You might also want to check out this blogpost for an alternative way: https://www.thebiccountant.com/2017/08/30/how-to-open-a-complex-json-record-in-power-bi-and-power-qu...

[Anonymous]

Thanks for the suggestion.  I tried that but it still has the records embedded.  I'll take a look at the link you provided to see if it has something I can use.  

[Anonymous · Accepted solution]

Thanks ImkeF, for the suggestion.

Just to recap the solution to this problem:

Problem: 

Web connection (URL) to Pipedrive pulls all requested data into a nest table so data cannot be seen unless individually selected.

Solution: 

Use the Web connection and once the data is received, use the Query Editor to 'convert to table' which will give you a table but each record is still nested.  Click on the icon in one of the column headers and it should convert all 'records' to their actual values.