Dynamically expanding all the columns containing lists/records received in a JSON response from APIs

Question

I am new to Power Query and working on Power BI.

There are multiple Web APIs that return a nested JSON response having multiple lists and records.

I need to write a single generic code to dynamically expand all the lists and records in any JSON response using Power Query, without knowing the names/number of the key-value pairs in it.

Also, the datatypes of the columns should be set automatically using the same code.

Any help is greatly appreciated.

Thanks in advance.

arif1033 · Answer

Hi, I used the M-code in the below link -

https://www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-power-bi-and-power-query/

bu now i see an i option like below (Image included) - Please let me know what should be the next steps here

after i click on Invoice i get an error (Image included)

imkef · Answer

I wrote a function for that some time ago:
https://www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-power-bi-and-power-query/&nbsp;

imkef · Answer

Hi&nbsp;Arif1033&nbsp;,instead of null you need to pass in the (reference to the) JSON that you want to analyze/expand.
&nbsp;

arif1033 · Answer

I apologise i did not quite get it - am new to power query and API's - am using the below rest API - can you give me some examples of what refrence i should be using it

https://XXXX.com/jira/rest/api/2/search?jql=project in (xxx,xxx) AND created >= startOfYear()&maxResults=1000

Forum Discussion

Dynamically expanding all the columns containing lists/records received in a JSON response from APIs

4 Replies

Related content

List Generate Average as record;

Expand column containing list of records

Expand list with records to columns

Extract Values From a List of Records

Creating a record from list values