cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
UsePowerBI
Post Prodigy
Post Prodigy

Can we specify in Power Query which local worksheet to load the data?

Hello

 

Can we specify in Power Query which local worksheet to load the data? And also to overwrite and delete old rows?

 

Thanks!

8 REPLIES 8
UsePowerBI
Post Prodigy
Post Prodigy

Thanks I was looking to do that within Power Query!

KNP_0-1634333407862.png

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?

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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.

edhans_0-1634319702316.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
KNP
Super User
Super User

@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...'

KNP_0-1634059570124.png

In the next dialogue box choose 'Table' and 'Existing worksheet'

KNP_1-1634059670351.png

 

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.

KNP_2-1634059817094.png

 

Example workbook attached.

 

If this answers your question, let me know and I'll make sure it gets marked as the solution.

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
v-kelly-msft
Community Support
Community Support

Hi @UsePowerBI ,

 

Put all the worksheets to a folder,then connect the folder to power bi.

For details,pls check below:

https://www.myonlinetraininghub.com/power-query-get-files-from-a-folder

In query editor,you could select which worksheet to load,once you have updated a new one,you could also remove the old one.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

Freed
New Member

Yes you can do this. 

@Freed  thanks but how exactly? Any example?

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071...

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors