How to extract a specific value from a list nested in a table

[AlbertoGP84]

Hi Everyone,

please have a look at the below screenshot.

I have a list nested in each row of my table and I need to extract only the information in the list about full filename ending either with .pdf or with .zip and then expand the value to my table in a new column.

In the screen shot you see as an example I have to extract the file name ending with .zip.

I have tried expand all values using the expand button and then filter only .zip or .pdf  but since it is superslow since data are taken from online location and the iteration is too long.

I would like to reduce the number of iteration and then expansion by selecting only the filename I need.

Any help would much appreciated! Thank you very much in advance. I tag @PhilipTreacy as he is the only person i know in this community. Thanks again!

Alberto

[PhilipTreacy · Accepted solution]

Hi @AlbertoGP84 

Download example XLSX file

Try this in a new column

try List.Select([Files], each Text.Contains( Text.Lower(_) , ".zip") or Text.Contains( Text.Lower(_) , ".pdf")){0} otherwise null

I don't know if the filenames will always be in lower case, so I'm forcing that by using the Text.Lower function to make sure the filename will match against either .pdf or .zip

I also don't know if there will always be a matching filename.  If there isn't an error will occur, so I've used try .. otherwise to trap that and output null in such cases.

If you are still having issues with this, please post some sample data.

Regards

Phil

[AlbertoGP84]

Hi Phil,

your explanations are always very clear and solutions are spot on.

Thank you very much, really appreciated!!

If you have any good course suggestion or e-book reading for the M-Language will be more than welcome.

Have a good day!

Alberto

[lbendlin]

Here is the best (by far) coverage of M:

https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi...

[lbendlin]

You can use List.FindText or List.Matches* functions.

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523