PowerQuery Refresh in Power BI

[cthardwik]

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

[MattAllington · Accepted solution]

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.  

[DataChant]

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]

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?