Help with extracting a JSON

[Anonymous]

Hi All,

So we have an applications portal where people can choose various answers and submit an application for funding.

I have managed to (mostly) successfully extract the "responses" JSON into a set of columns, however there are a few where the response comes through as List.

In a further complication, some entries are empty, I have rplaced some with zeroes (where an error appeared in the original extrac), and the list entries can be different sizes:

And when I try to split the column by delimiter (I've tried them all and am not certain what the delimietr actually is) it gives me an error on converting a List to Text

I'm out of ideas. Any suggestions?

Thanks.

[amitchandak]

@Anonymous , Check if you have a expand icon on the column two 90 degrees angle opposite to each other. If it there uses that and expands it to row and columns using that

refer these

https://www.youtube.com/watch?v=sbPS3ZKIt7g

https://powerbi.microsoft.com/en-us/blog/how-to-expand-a-list-of-records-in-a-query/

[Anonymous]

@amitchandak no, the column doesn't expand or have the expansion icons. I suspect it's because the "List" is of different lengths per record (could be anywhere from 0 to 6 list entries).

[Anonymous · Accepted solution]

Success!

Instead of replacing errors with 0, I removed them instead. I can now extract the list to individual rows and perform my calculations from there.

I'm learning!

[v-xuding-msft]

Hi @Anonymous ,

Glad that you have resolved it. You could accept your answer as solution. More people who have the same requirement will benefit from this thread.