Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AlbertoGP84
Regular Visitor

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

Hi Everyone,

please have a look at the below screenshot.

AlbertoGP84_0-1670624309248.png

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

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
PhilipTreacy
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi Phil,

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

AlbertoGP84_1-1670831172538.png

 

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
Super User
Super User

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors