Forum Discussion
Web connection pulls all data but every record is nested rather than placed in a flat table
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"))
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.
5 Replies
Please paste pic of how the records are nested. (Can give solution just based on that)
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-query/
