cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cthardwik Frequent Visitor
Frequent Visitor

PowerQuery Refresh in Power BI

So basically I have lots of different spreadsheets with different data for different projects. In each of the spreadsheets I reguarly update the data, but do not chagne the columns and headings. However to save time I transform the data in PowerQuery in Excel, then upload the PowerQuery table to Power BI to create my reports. However when I then go back to my individual spreadsheets to change the data,  I have to manually press 'Refresh All' under the 'Data' tab for my PowerQuery table to update. 

 

Is there any way where I can just update my data in the individual files, save it, close it, and then when I press 'Refresh' in power BI, it reads the updated data (ie the power query table in excel updates)? Or a way to update the PowerQuery table when I save and close each spreadsheet?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: PowerQuery Refresh in Power BI

If I understand correctly....

 

1 Your source data is in Excel

2 Your transformation of this same data is in Power Query for Excel - in the same file

3 You then import to Power BI for reporting

 

If this is correct, the problem is that you can't refresh 3 and have it trigger a refresh of 2.  The solution is to move the query from power query in 2 and move that into power query in 3.  That way you Power BI file will do the conversion on refresh.  You can schedule a time based refresh in Power BI.  You will need either a gateway to connect to your PC or have your Excel files replicated using OneDrive.  Don't be scared of the gateway - it is easy to use.  I recommend installing the enterprise version, not the personal version.  



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

3 REPLIES 3
DataChant Member
Member

Re: PowerQuery Refresh in Power BI

If you'll host the Excel files on OneDrive for Business, and the Power BI report will load the data following this guide, you can set up a daily auto refresh on Power BI free version, or a hourly refresh if you are on Power BI Pro.

cthardwik Frequent Visitor
Frequent Visitor

Re: PowerQuery Refresh in Power BI

I have tried this solution, however when i go to refresh in Power BI online, it says

 

Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again.

 

Do you know why this wouldn't work?

 

Highlighted

Re: PowerQuery Refresh in Power BI

If I understand correctly....

 

1 Your source data is in Excel

2 Your transformation of this same data is in Power Query for Excel - in the same file

3 You then import to Power BI for reporting

 

If this is correct, the problem is that you can't refresh 3 and have it trigger a refresh of 2.  The solution is to move the query from power query in 2 and move that into power query in 3.  That way you Power BI file will do the conversion on refresh.  You can schedule a time based refresh in Power BI.  You will need either a gateway to connect to your PC or have your Excel files replicated using OneDrive.  Don't be scared of the gateway - it is easy to use.  I recommend installing the enterprise version, not the personal version.  



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,663)