Whether you’re managing invoices, project documents, or daily reports, creating a log of all file uploads in a folder can be a valuable tool for tracking workflows, configuring updates, and more.
This post explores how to use Power Automate to create a dynamic flow that automatically adds a row to an Excel table every time a file is uploaded to a OneDrive folder. We’ll guide you through the process step by step and compare this approach to a well-known alternative using Power Query. To get started, download the exercise file below and upload it to the OneDrive account associated with your Power Automate subscription:
Unzip the files and place them in your OneDrive.
In this folder, you’ll find a starter Excel workbook containing an empty table as shown below. We will create a script that triggers every time new files are added to the workbook. Since the placeholder.txt file is already placed in the folder before you start running these scripts, it won’t end up in the folder.
Next, head over to make.powerautomate.com and create a new automated cloud flow. This flow is triggered by a specific event, particularly when a file is created. You can use the “When a file is created (properties only)” flow type by searching for it in Power Automate, and ensure it points to the correct folder for incoming files.
You’ll also be prompted to set how frequently the flow checks for new uploads. You can set it as often as every 60 seconds, although this may become tedious and consume too many resources. Consider setting it to 60 seconds initially to ensure it’s working correctly, then later adjust it to a more manageable schedule, like once or twice an hour.
Great! The next step is to insert a row into our table whenever a new file is created. Be sure to reference the correct workbook and table, and then, under the parameters, ensure you’re passing dynamic data for the Name, Last Modified Time, and Size.
To format the Last Modified Time in my local timezone and make it more visually appealing in Excel, I’ll use the following Power Fx function to adjust the formatting:
formatDateTime(convertTimeZone(triggerOutputs()?[‘body/LastModified’], ‘UTC’, ‘Eastern Standard Time’), ‘dd MMM yyyy HH:mm’)
Great job so far. Now that we have set up this flow let’s go ahead and save it and to test it. To do so, add a couple of test files, any kind, to the incoming-files folder and wait a few seconds for Power Automate to re-sweep through the folder to check for new files.
Once that happens, you’ll see these files listed in your table.
You can further enhance these outputs by formatting the Size column, adding the file extension type in a separate column, and more. However, this process will continuously add rows without modifying existing ones. This leads us to a tool better suited for maintaining and refreshing a current list of files in a folder: Power Query.
Listing new files in Power Automate versus listing all files in Power Query
Before we wrap up this post, I want to offer a quick comparison between the Power Automate feature and Power Query for listing all files in a folder. If you’ve never done this before, check out this detailed guide from our friend Mark at Excel Off The Grid.
So, what are the differences between these two approaches, and when should you use each?
Power Automate Flow and Power Query serve different purposes in workflow automation and data management. Power Automate is designed to trigger actions automatically based on specified events, such as new file creation. It’s ideal for real-time responses and can handle complex workflows with extensive integration across services. However, it requires a deeper understanding and more initial setup.
On the other hand, Power Query is focused on extracting and transforming data at the time of query refreshes, making it well-suited for static reports and periodic analysis. It is simpler to use and set up, primarily integrating with data sources that are directly accessible to Excel.
The following table provides a summary of this comparison:
What questions do you have about logging files with Power Automate, or about the comparisons and differences between Power Query and Power Automate? Let me know in the comments.
Leave a Reply