7 METHODS to change source data location in Power Query

No video

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

Рет қаралды 24,827

Excel Off The Grid

Күн бұрын

Пікірлер: 30

@IvanCortinas_ES Жыл бұрын

Excellent tutorial Mark. Thank you for such interesting content.

@ExcelOffTheGrid Жыл бұрын

My pleasure!

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

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

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

@sankethingne 10 ай бұрын

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 10 ай бұрын

Glad it helped! 😀

@garyrowe58 13 күн бұрын

Me too! It's disheartening to have to spend so much time searching until you a) find the right term(s) to search for, and then (b) find the right video! The only upside is that you learn lots of other things (hopefully usefull) during the search ;-)

@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 😂

@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 😀

@abuibrahim5178 2 ай бұрын

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?

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

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

@therevenant211 Ай бұрын

Will these methods work if the issue is that other users cannot refresh the file? For example, I have 3 different data sources driving queries in a tool that I need to make usable for my employees. But none of them can refresh the file because the filepath has my userid in the filepath. So would I need to create a named cell that contains a filepath that adjusts that userid section of the path depending on who is doing the refreshing, or am I overthinking it?

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

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

@ExcelOffTheGrid 11 ай бұрын

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.

@ajayrathod7777 7 ай бұрын

How to create this for two excel files,one is for source excel file and other is mapping excel file

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

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!

@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

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

@dianat5710 9 ай бұрын

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!

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