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
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
Solved! Go to Solution.
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 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
Proud to be a Super User!
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 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
Proud to be a Super User!
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:
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |