7 METHODS to change source data location in Power Query

7 METHODS to change source data location in Power Query | Excel Off The Grid

Рет қаралды 23,053

Күн бұрын

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post ★
exceloffthegrid.com/power-que...
★ About this video ★
Maybe you’ve built some queries in a test environment, and now it’s time to start using them for real. The obvious question arises: How to change source data location in Power Query?
0:00 Introduction
0:47 #1 Edit the source step
1:24 #2 Change the M code (formula bar)
1:45 #3 Change the M code (advanced editor)
2:09 #4 Data source settings
2:59 #5 Worksheet cell parameters
4:02 #6 Queries as parameters
4:34 #7 Power Query parameters
5:21 Conclusion
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel #PowerQuery

Пікірлер: 28

@jamesregan4791 Жыл бұрын

This is a perfect tutorial on changing the data source, after seeing 6 this one is direct to the point on changing data sources thank you.

@IvanCortinas_ES Жыл бұрын

Excellent tutorial Mark. Thank you for such interesting content.

@ExcelOffTheGrid Жыл бұрын

My pleasure!

@rigobertogarcia Жыл бұрын

Great, great and more great Mark. Thank you very much for your contributions that feed our professional development. Thank you

@ExcelOffTheGrid Жыл бұрын

You’re welcome 😀

@kenmcmillan2637 Жыл бұрын

Thanks for the review Mark. My goodness, you have an incredibly clear camera!! Super sharp image!!

@ExcelOffTheGrid Жыл бұрын

Thanks. Not sure why, it’s the same camera as before. I am trying to up my game. Will be using a new camera and monitor next year, so we will see how that improves things further.

@iankr Жыл бұрын

These are all sources on a network share. It's a whole lot more fun if you're working with files stored on SharePoint, as companies are increasingly doing these days. Microsoft needs to make this much simpler!

@ExcelOffTheGrid Жыл бұрын

Agreed. I think the same principles apply for SharePoint or Network drives. The challenge is getting the file path to change it to, as we need to look in the "secret" information location 😂

@mojtabareyhani7905 Жыл бұрын

Thanks a lot for your great tutorial. Please create a video about how can create a flexible and reusable power query structure.

@ExcelOffTheGrid Жыл бұрын

I was thinking about this yesterday. I'm not sure anybody has done a video about that. I'll add it to my list of possible topics.

@sankethingne 9 ай бұрын

i wasted my a lot of time to find how to do this and this video within minutes directed to the point solution.....fantastic.... 😊

@ExcelOffTheGrid 9 ай бұрын

Glad it helped! 😀

@chriswall4795 Жыл бұрын

The excel named range can be adapted to pick up where file is saved and pass that to PQ. Then data can be in a subfolder called data relative to where the PQ file is ... Your methods all rely on hard coded paths. We break them into serverpath projectpath and filename parameters then concatenate them in PQ for greater flexibility.

@ExcelOffTheGrid Жыл бұрын

For my projects I follows a similar method of including different parts of the string. I tend to concatenation in Excel rather than in PQ, but it’s the same approach.

@abuibrahim5178 Ай бұрын

Thanks for tutorial sir Please answer my question sir What happened if I just rename the data source? Is there changing the data and all the stepped I run in my power query?

@Sumanth1601 Жыл бұрын

Excellent content.. Good for beginners.. however the one I switched to using the most is under the Data ribbon > Get data > Data source settings. I don't have to go to power query window.

@ExcelOffTheGrid Жыл бұрын

Yes, that's a good one. Similar to #4. My favorite is the cell based parameter, then I don't even need to open up anything. It takes a few extra seconds to get around the Formula.Firewall error, but then it's as easy as typing text into a cell.

@arungupta8444 Жыл бұрын

Hi, What if the Source File is used for the Merge + Append + Group transformations with other files . but now the source file got Updated with Lots of Data and it is difficult to update the file and we need to replace the source file alltogether, with new file with same name, without affecting the previous output. Can it be done with "Parameters" Setting or something else. Please Advise.

@ximox Жыл бұрын

Hi Mark, how you recommed to use a path_parameter of folder to open at once all the file in that folder.

@ExcelOffTheGrid Жыл бұрын

I would use this method: kzfaq.info/get/bejne/q7aAoZWWz9W-YaM.html

@dianat5710 8 ай бұрын

In my power query, the source is a separate excel worksheet. How can I change it to an online source? I need it to connect directly to a salesforce report. I haven’t been able to find out how to change this. Please please please let me know if theres a way!! Thank youuu!

@dean.beenaliza 10 ай бұрын

How about changing the source from another workbook to the data in the current workbook which has the same raw data table?

@ExcelOffTheGrid 10 ай бұрын

From Power Query’s perspective that is a different connector. So you’ll need to re-create the initial steps, then build an if statement which uses the different connectors depending on the scenario. Possible, but definitely some M code required for that.

@YWang-gr3yp 9 ай бұрын

I followed exactly your steps, but when I go to data source seting, the "Change Source" button is greyed out, please advise, thanks a lot!

@Regina.Falange Жыл бұрын

What if I want to change the source from an external file's table to a table inside my document?

@ExcelOffTheGrid Жыл бұрын

You’ll need to make changes to the M code. Create a new query to connect to the file, then take the parts of the code you need and insert into the original query. Make sure you take a backup. It can be a bit daunting, and very error prone if you’ve not tried to work with M code before. The last option is the rebuild the query from scratch. But try to avoid doing that if you are able to.