Can we specify in Power Query which local worksheet to load the data? And also to overwrite and delete old rows?
Technically, the answer to your questions are, no and it overwrites and deletes by default.
I'm struggling to understand the use case here. Once you set the load location to the worksheet you want, it persists. Why does it need to be done in Power Query?
Yes, @KNP has the correct answer. If you agree and it helps you @UsePowerBI please mark it as the solution. As an additional bit of info, I'm not sure why Power Query in excel requires you to choose loading in Excel vs in Power Query. Power BI's Power Query is done in Power Query itself.
But, in Excel's Power Query you can change the defaults of how it does work. When you install, all queries are loaded to a sheet. I disable that and load them as I need to on a case by case basis. It is in Power Query, in the Options dialog box.
@UsePowerBI - I've just re-read your question and I think I understand now what you're saying. I don't think the answer that has been marked as a solution is correct.
If you right click on your query and choose 'Load To...'
In the next dialogue box choose 'Table' and 'Existing worksheet'
This will let you specify the location and click load.
By default data will be replaced by the query but there are also some additional settings if you go to the table properties.
Example workbook attached.
If this answers your question, let me know and I'll make sure it gets marked as the solution.
Hi @UsePowerBI ,
Put all the worksheets to a folder,then connect the folder to power bi.
For details,pls check below:
In query editor,you could select which worksheet to load,once you have updated a new one,you could also remove the old one.
Did I answer your question? Mark my reply as a solution!
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.