Hi,
- Do Power Query Auto Refresh if the Excel sheet is closed?
I have external data that downloads (once a day) to an Excel file which I have saved in OneDrive for Business.
I have built a Power Query table, in this excel file, performing data cleaning, etc. And set the Power Query refresh (in properties) to refresh every 10 minutes based, with the background tick on.
Then I pulled the Power Query file into Power BI to start building my dashboards.
The data on the download file has since changed, but the Power Query table is not refreshing automatically. (I opened the Excel file and checked, the table did not refresh.)
Using Excel 2013.
Any ideas on how to make the auto-refresh work? As I am trying to automate the data refresh before it gets uploaded to Power BI.
Thanks in advance, Vicky
Hi Vicky,
Queries in Excel will only refresh if Excel is open.
If you're building a Power BI report, I recommend you move the query to Power BI, i.e. From Power BI get the data from Excel and do the cleaning in Power BI. This way when you publish it to the Power BI service it will have the connection direct to OneDrive (rather than via the Excel file) and therefore will automatically update once per hour.
Mynda
Hi Mynda, thank you
I was hoping to do data cleaning before the file gets to Power BI, as I have several huge files that I must use in my Power BI project.
I know Power BI gets very slow and sometimes unusable if it has to work too hard.
Also, I have one column (for each of my data files) which keeps on changing with each download. (Log column.) I wanted to exclude that column from going into Power BI, by selecting a data range.
Regards
Vicky
Hi Vicky,
If you get the data with Power Query you are doing the cleaning before the data gets to the Power BI model. i.e. it doesn't matter if you get the data with Excel's Power Query or Power BI's Power Query, the process is the same and just as efficient.
You can exclude columns from your model by deleting it in Power Query before closing and loading.
To be clear, when you connect to a data source using Power Query (whether in Excel or Power BI), Power Query only gets a snapshot of the data an displays that in the Power Query editor (this is why it never shows more than 999+ records in the bottom left of the window). You then use the tools in Power Query to clean and shape the data, removing rows and columns etc. that you don't need. When you 'Close & Load' the query, it only loads the data that matches the criteria in the query. So, getting the data using Power Query in Excel, and then loading the Excel file to Power Query in Power BI is just as efficient as getting the data with Power Query in Power BI.
I hope that clears things up and you're now comfortable with using Power Query in Power BI to get the data directly, rather than via Excel.
Mynda
