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
Super User
Super User

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.

Highlighted
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?

 

Super User
Super User

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 Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)