iNsync: Set Up Power BI Automatic Refresh with Excel
Jan 31, 2024
Resources to help build your fundamentals: Learning Power BI? Data fundamentals are critical for this process to be easier. I've assembled a few DataCamp courses to help. I earn a small commission if you click and purchase.
Introduction to Power BI course: Master Power BI basics and learn to build impactful reports.
Exploratory Data Analysis in Power BI course: Learn about Power BI's Exploratory Data Analysis (EDA) and enhance your reports.
Introduction to SQL course: Learn how to create and query relational databases using SQL in just two hours.
Introduction to DAX course: Learn the fundamentals of Data Analysis Expressions (DAX) such as calculated columns, tables, and measures.
Database Design course: Learn to design databases in SQL to process, store, and organize data in a more efficient way.
Imagine this: You've got a Power BI report, and on the other side, an Excel sheet constantly updated with fresh data.
Now, wouldn't it be great if your Power BI visuals got a refresh every time Excel had something new to say?
It's like having your coffee machine sync with your alarm - the moment you're up, your caffeine dose is ready!
Let’s explore how to get Power BI and Excel working well together with automatic refresh.
The key here is to use SharePoint.
Why?
No Gateway required. If you save an Excel file on your local machine and try to set up Automatic Refresh, a Gateway is required for set up.
This works ok, but you can't go on vacation. Ask me how I know.
Your computer has to be turned on for this to work, which means if you need Automatic Refreshes while you're on the beach, you have to have your computer turned on while on the beach.
Not ideal.
So - the best way is to save your Excel file on SharePoint, which is 'always on'. And since it's in the Microsoft ecosystem, Power BI and SharePoint talk to each other easily.
Foundations First:
Before we jump into it, save your Excel file on your local machine.
What? I just cautioned against this!
Don't panic - this is only a temporary step. We'll move it to SharePoint in a minute.
It's easiest to create this desktop connection first, through the Get Data from 'Excel Workbook' method.
(By the way, need help finding your data sources? Read this article.)
Feeding Power BI with Excel:
In Power BI Desktop, connect to your locally saved Excel File. Under the 'Home' Ribbon, select 'Excel Workbook'.
Move Your File To SharePoint
Next, drag your file over to a SharePoint folder. Copy the URL location. Then, go back to Power BI Desktop and redirect the Power BI linkage to the SharePoint location. Go to File > Options and Settings > Data Source Settings > Change Source. Paste in your SharePoint URL.
Scheduling the Sync-Up:
Alright, here’s where we set the rhythm. If you haven't yet, Publish your Power BI to the Online Service.
Then, head over to Power BI Service (online), find your dataset, and navigate into the 'Scheduled Refresh' section.
Set up your refresh frequency.
Whether you want it daily, multiple times a day, or even weekly, tune it to your Excel file's update rhythm.
No More Manual Pushes:
Every time your Excel file gets new data, it updates in the cloud.
And thanks to our scheduled sync-up, Power BI will catch that change and update your visuals accordingly.
No manual intervention, no repeated uploads.
It's automation at its finest!
A Little Tip:
Keep an eye on the dataset's 'Last Refresh' time in the Power BI Service.It’s a nifty way to confirm that the automatic refreshes are happening as expected.
In Closing:
Marrying Power BI's analytical prowess with Excel's data versatility creates a dynamic duo, unbeatable in its efficiency.
By setting up this automated refresh, you're not just saving time.
You're making sure your insights are always fueled by the freshest data.
So, here's to always being in sync and letting automation take the wheel! 🔄📊📈
Download My Free Guidebook
The 3-Step Plan for Getting Started With PowerBI
We hate SPAM. We will never sell your information, for any reason.