I often work with clients who receive data files on a regular basis—whether it’s a monthly sales report or a weekly inventory list. Their routine usually involves saving the file in a specific location, opening it, copying the data, pasting it into another file, and then formatting it. It’s a repetitive process that can be both time-consuming and tedious.
Wouldn't it be nice if there was an easy way to streamline this process so that all they had to do was drop the file into a designated folder, and the rest would be automated? In the past, I’ve built apps to achieve this using Windows Job Scheduler, but managing multiple scheduled tasks can be cumbersome and prone to issues.
That’s why I’m thrilled about a new feature in Power BI that simplifies this process!
Here’s how it works:
Organize Your Files: Start by placing your files into a specific folder—let’s call it “2024 Sales Reports”. Each time you receive a new sales report, simply add it to this folder with a descriptive name, such as “January 2024 Sales Summary”.
Set Up Power BI Connection: In Power BI, create a connection to the folder. Instruct Power BI to include all files within this folder. This way, any new file added will be automatically incorporated into your data set.
Create Reports and Dashboards: Develop your reports or dashboards based on this connection. Power BI will pull data from all files in the folder, so your reports will always reflect the latest information.
Important Considerations:
Consistency is Key: Ensure that all files you place in the folder have a consistent format (e.g., all should be in .txt or .csv) and contain the same columns. Changes in file format or structure can lead to errors or issues in your reports.
Automated Updates: Once set up, Power BI will automatically check the folder for new files each time you open the app. This means there’s no need for manual refreshing, requerying, or reformatting.
If you can maintain consistency in your file formats and structures, this feature can save you a significant amount of time and hassle.
Here's a quick example. I've got a personal budget set up in Excel. Here you can see the first three months of 2024:
I store this files in a folder:
I created a dashboard for this data in Power BI, and you can see the January, February and March data:
So far, so good. Now I create my April budget and drop it into the same folder:
When I re-open the Power BI, it tells me that there is a pending update. I tell it to go ahead and load the new data. When its done, my dashboard is automatically updated to include the April data:
Such a great solution! Now you can enjoy a more efficient way to manage your data and focus more on analyzing the insights rather than dealing with the data preparation!
Need help setting up your Power BI? Drop me a line.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2024. All rights reserved.
Comments