Power Automate: Fetch all files from a type in a folder

Today something that looks simple, but it’s a bit tricker than it sounds. We’re going to fetch files from a folder but only from a specific type, for example, all Excel files in a folder. So the idea is to have a template that we can invoke to get all files from a type in a folder and parse them accordingly.

Get “Media Type” of a file

Get the file’s “Media Type” to filter all files of a type in a folder for example. 

Finally, here’s the solution with both the template above and the example in this article:

Solution with the “Get Media Type of a file” and “Filter files of a type”

Here’s the solution with a working example of how to fetch all the files of a type in a folder, using the “Get media type” template above.

There are a few things to understand, so let’s get started.

The setup

The template will use a SharePoint Document Library, but you can use OneDrive or even Microsoft Teams. It works the same way across all of them and what’s important is the strategy.

So we’re going to do the following:

  1. First, fetch all files from a folder.
  2. Filter by the type that we want

That’s it, but the second step has a tricky part—the type itself.

It’s easy to fetch files in a folder. First, we can use SharePoint’s List folder action to return all the files in a folder. After that, we have the “MediaType” field that will return the file type, and we can filter by that. But here’s what it returns:

Let’s expand it a bit (removed the properties that are not interesting for now):

[
  {
    "Name": "Document.docx",
    "DisplayName": "Document.docx",
    "Path": "/Shared Documents/Test Files/Document.docx",
    "Size": 17138,
    "MediaType": "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
    "IsFolder": false
  },
  {
    "Name": "2021-11-01 10.22.51.gif",
    "DisplayName": "2021-11-01 10.22.51.gif",
    "Path": "/Shared Documents/Test Files/2021-11-01 10.22.51.gif",
    "Size": 171330,
    "MediaType": "image/gif",
    "IsFolder": false
  },
  {
    "Name": "Book.xlsx",
    "DisplayName": "Book.xlsx",
    "Path": "/Shared Documents/Test Files/Book.xlsx",
    "Size": 12972,
    "MediaType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    "IsFolder": false
  }
]

As you can see above, we understand what we have a word, gif, and excel files, respectively, but look at the “MediaType”. The Excel, for example, is “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”. Quite an ugly representation, right?

There’s not much that we can do about the types since they are part of a standard definition, but we can hide all the ugliness behind a Flow that takes care of all of this. So let’s build it.

The template

So now that we know what to look for let’s build a template that returns us “something” that we can filter on.

There are hundreds of “MediaTypes” out there and more being generated so it’s tricky to accommodate for all of them. I’ll add more over time but, for now, I’ll add only the most common ones. The strategy is simple, so if one is missing, you can easily add them.
Also, if you want to submit a template with more “MediaTypes”, I’m more than happy to feature you.

Here’s the template. First, we define a static list of items to choose from.

Then, depending on the type, we’ll return the “code”.

It’s pretty simple, but you need to know what to insert.

Fetching the rows

Now that we have a template to do all the heavy lifting for us, here’s how to filter the files in the folder.

We fetch the files with SharePoint’s List folder action and then filter with the value returned from the Flow before. Here’s what we get:

[
  {
    "Name": "Book.xlsx",
    "DisplayName": "Book.xlsx",
    "Path": "/Shared Documents/Test Files/Book.xlsx",
    "Size": 12972,
    "MediaType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    "IsFolder": false
  }
] 

We see that we only get Excel files, so it’s easier to now parse the files.

Final thoughts

The Flow to fetch all files from a type in a folder is simple once we understand the “MediaType” part. Of course, these conventions don’t make things simple, but the internet was built on patterns like this.

The “Get MediaType” template will help deal with more strange cases, and as always, we separate things that are useful in another Flow to re-use them in other Flows.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Power Automate-related articles here.

Photo by Christina Rumpf on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: