TL;DR: Filter SharePoint files in Power Automate with the FileLeafRef, FileRef and FileDirRef fields via simple OData queries.

💡 Challenge

Filtering SharePoint documents in Power Automate gets tricky when all you have is a file name or folder path. The right filter fields aren’t obvious, so many makers end up fetching every file and filtering inside a loop, slow and inefficient.

✅ Solution

Use SharePoint’s built-in file-reference fields, FileLeafRef, FileRef and FileDirRef, directly in your OData Filter Query to return exactly the files you need, server-side.

🔧 How It’s Done

Add one of these fields to the Filter Query of your “Get files (properties only)” action:

🔸 FileLeafRef, filter by file name (without the path), e.g. FileLeafRef eq 'Invoice.pdf'.

🔸 FileRef, filter by the full path to a file.

🔸 FileDirRef, filter by files within a specific folder.

This pairs well with efficient query design, see #PowerPlatformTip 95, ‘Optimized SharePoint Queries’.

📦 Exclude or Include Files by Extension with substringof

A very common requirement: return all files except a certain type. Use substringof() on FileLeafRef with eq false:

substringof('.zip', FileLeafRef) eq false

This returns every file whose name does not contain .zip. The reverse works too – only Word documents:

substringof('.docx', FileLeafRef) eq true

And both patterns can be combined with other conditions:

substringof('.zip', FileLeafRef) eq false and startswith(FileDirRef, '/sites/demo/Shared Documents')

Note: substringof('.zip', FileLeafRef) matches anywhere in the file name – a file called archive.zip.backup.docx would also match. For a strict extension check, sort by name or verify the extension afterwards with an expression like endsWith() in a condition.

🎉 Result

Your workflows become more robust and easier to maintain. Server-side filtering cuts the time spent on manual adjustments and minimizes the potential for errors.

🌟 Key Advantages

🔸 Improved efficiency: faster development through server-side filtering.

🔸 Better consistency: a standardized approach across all projects.

🔸 Enhanced reliability: reduced risk of failure during execution.

🎥 Video Tutorial

🛠️ FAQ

1. How do I exclude ZIP files (or any extension) with an OData filter? Use substringof('.zip', FileLeafRef) eq false in the Filter Query – it returns every file whose name does not contain .zip. With eq true you include only matching files instead.

2. How do I apply these OData filters in Power Automate? In the “Get files (properties only)” action, enter your OData filter expression into the Filter Query field to limit results based on FileLeafRef, FileRef, or FileDirRef.

3. Can I combine multiple OData filter conditions? Yes. Use logical operators like and or or in your filter query, for example: FileLeafRef eq 'document.pdf' and startswith(FileDirRef, '/sites/demo/Shared Documents').

4. Are these fields supported in SharePoint on-premises? OData filtering with FileLeafRef, FileRef, and FileDirRef works in SharePoint Online and on-premises (2013+). Ensure your environment and connector version support OData queries.

5. What does FileLeafRef mean? It’s the file name itself, for example, ‘Invoice.pdf’.

6. What does FileRef mean? It’s the full path of a file, including folders, for example, ‘/sites/demo/Shared Documents/Invoice.pdf’.

7. What does FileDirRef mean? It refers to a folder path only, for example, ‘/sites/demo/Shared Documents/’.

🔗 Related Tips

Need Help with Your Power Platform Project?

Get expert guidance through personalized workshops, consulting, and training tailored to your specific needs.

Request Workshop

Consulting and process automation for SMBs is delivered by KMUpower. Prefer structured, German-language courses? The Power Platform Academy offers live Power Platform Schulung and hands-on Power Platform courses for Power Apps, Power Automate and Copilot.