From excel to Power BI to Power Query: how can I make it easier for a user to update a query

0
votes

I have a power BI mixing several excel files in a folder.

I then copied the query in the "queries and connection" of excel proving the data of the query in BPI.

New files arrive periodically, the old version are suppressed and it is replaced by the new file, with new data. The user presses "refresh" in excel and he has a refreshed query.

Is this making sense? Is there a way to do it more simply and better for a user wanting to get up to date data in the excel?

Following the comments and answer I am adding some information here:

I have two excel files which I am merging in power BI and over which I am operating a variety of additional operations:

enter image description here

I do that in the following power BI file:

enter image description here

I go into queries and I copy the resulting merged query, looking for merged rabbits: enter image description here

I then copy the query in Excel in the "Queries and connections" pannel: enter image description here

My question is thus: how could I enable the end result, my excel to refresh with the last data, without having to redo this cumbersome operation? If my two source files are updated, I would like my user to simply press refresh without having to do the whole process again.

1
excelpowerbirefreshpowerquery
I'm sorry, for me at least, this needs a bit more clarification. You have a Power BI file that's reading data from multiple excel files? And you want the data from this Power BI file to be accessible from yet another excel file? – Gigga
Yes! I am sorry, I will try to make it clearer by updating the question. The Power BI files merges excel files. Then I copied the query and build it within excel. I would like to then, be able to refresh the query inside my excel when the data in the two files change. – Tomas Michel
PowerQuery can be used inside Excel, therefore you should be able to replicate whatever you did in PowerBI in excel (which is what you are doing now). The query is valid as long as the structure of the data is the same, if you have to repeat the process, I guess it's because the data structure has changed. If that's the case, there is no way you can avoid editing the query manually. – Giovanni Luisotto

1 Answers

1
votes

If I got it right, you have a query in excel (in the "Data"→"Queries & Connection" section). If this is the case, you can choose to update the data on fixed interval or on file opening.

open "Queries & Connection", right click the query and choose "properties", from there, in the "usage" tab, you can set it to auto-refresh on file open.

enter image description here