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

[Anonymous]

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.

[ImkeF]

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

[Arif1033]

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()&maxRes... 

[Arif1033]

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-po...

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]

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-po...