Forum Discussion
How to extract a specific value from a list nested in a table
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
Hi AlbertoGP84
Try this in a new column
try List.Select([Files], each Text.Contains( Text.Lower(_) , ".zip") or Text.Contains( Text.Lower(_) , ".pdf")){0} otherwise nullI 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
4 Replies
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-p/963216
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/1447523Hi AlbertoGP84
Try this in a new column
try List.Select([Files], each Text.Contains( Text.Lower(_) , ".zip") or Text.Contains( Text.Lower(_) , ".pdf")){0} otherwise nullI 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
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
Here is the best (by far) coverage of M:
