Extract data from list in query editor

Hello,

I have a dataset coming in from a survey that contains list like the image below.

I am trying to extract the data out from the list and show it in the same row. I created a custom column and applied the following formula:

Record.Field([test]{[Index]}, “test”)

However, it throws an error whenever I do that and says
image

I was wondering if there is anything I might be missing on my steps. Or, if there is another way to extract them.

Thank you

Hi @supergallagher25,

The issue is that you also have empty text strings in this column (and an empty string is not of type list)

Two ways around this add try otherwise to catch any error OR add an if then else statement to perform a test before extracting values from the list. For example:

if [test]= “” then “” else Record.Field([test]{[Index]}, “test”)

I hope this is helpful.

3 Likes

Thank you Melissa, I tried the step above and got it to work. However, afterwards the following error took place in the next step.
image

That means the index > the number of elements in the list

add a question mark to return a null, instead of an error
[test]{[Index]}?

Thank you for your time, I had forgotten to mention that I had tried that as well afterwards and it gave another error.
image

Just extend the conditional logic, to resolve this error too.

if [test]= “” then “” else if [test]{[Index]}? = null then “” else Record.Field([test]{[Index]}, “test”)

I hope this is helpful

1 Like

My apologies, it now results in a blank column. I can clearly see in my other column that the list holds values such as this:
image

However, the custom column shows blank data. I am trying to extract out the data and put them all in one row for each response…

Hi @supergallagher25,

Without an actual sample, I’m unable to provide further assistance…

Hi @supergallagher25, we’ve noticed that no response has been received from you since the 7th of May. Please provide the file requested by Melissa to receive further assistance. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. Thanks

Hi @supergallagher25, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!