How to Fix the DataSource.Error Message in Power Query

hi in this video i'm showing you how to do with the datasource.error message and this is probably a message that you would frequently see if you are sharing files uh your kind of intermediate power query users and your and your sharing files amongst peers so let's see what that looks like [Music] say we're in a situation where we have carrie and ian and they're both working on the same project now kerry puts together a budget set and it's going to be the source for the analysis they're going to be working on it's a large file so instead of sending this file back and forth each would have the same local copy so when kerry sends it to ian he puts it onto his own documents folder and now she uses a new file to do her analysis on her local budget file she sends that to ian so they can collaborate on the analysis and when he opens up the power cord refresh to carry he gets this data source error now ian decides that in the power query steps he's going to put the file under a different path in the source step and he does analysis he sends it back to carrie changes the source in the power query sends it back to carry and she opens it and guess what happens when she refreshes the file well she's going to get the same data source error so let's see how we can rectify this now there's a couple ways you can do it this is just one of the ways and it's going to be using the parameter feature in power query here let's simulate it in how carry would do it initially so the data file is already in carries local path and i've opened up this analysis file so we're just going to pull it in from power query with power query so go get data from file and i'll just go browse for that file so here i browse for the file you see it's under users carry documents data open that up and it's going to open up in the power query navigator it's a table in that file click on the table click transform data and i'll show you where it looks up in the source code when the source step for that file so if we look at the source here you can see that it's looking in that directory path right i'll just do some brief steps maybe just do a filter just to see that it's doing something or just so that it's in something we'll just filter for engineering click close and load it's going to pull up that file now so here it is this is carrie with the perspective that she's working on on her own pc and she's got the analysis file and it's all local and it's looking at her my document her documents folder on that path now she sends this file over to ian of course ian has that budget file in his own directory path so let's see what that looks like so i'm going to simulate being on ian's desktop of course i don't i can't do two people but so let's say that that data file is in ian's of path now so we'll just move it over there so now we have the data file in ian's path right so if we go back to the analysis file and that's the one that kerry sent over to ian it should not work because it's trying to find it in this directory path so here i'm in the analysis file i just pretend that we sent it over to ian and he decides to refresh it and he's going to get this error and it's trying to look for it under the carry folder but it's not theirs so how to deal with that to make sure that they each get their own path to find that file we just create a parameter and they can just change that parameter each time they get the file we'll go back into the table query double click that and all we need to do is just create a parameter and then change the source where it's looking for it so what we can do here is create a new parameter and there's many ways we can do this we can have it where the parameter is a very common file path that is common amongst carry and an en or we can just make it where it's their specific file path and all they need to do is just change this parameter each time they get the file now we go to new parameter and we'll just call this file path or just make it short just call it path and for the current value it's going to be the path of that user's directory right users and then we'll say for him it'll be ian and then it'll be documents so it was users in documents right users ian document so now we've created a name for that path the parameter name click ok and that's fine now we'll take that path name and use it as a source for our query that's called table one double click that query go into the source and we're gonna put that path name there path and then ampersand space ampersand and delete go ahead and delete everything until we get to the file name because that's where it resides in that particular user ian documents folder path press enter and now we should see that it works right so you can see that so if i decide to work if i'm ian and then i decide oh i don't want engineering i want to do accounting i click ok it works click close and load and i send this back to carrie and let's simulate carrie's perspective back back to carrie's perspective so with kerry's perspective she doesn't have this ian document path let's get rid of that put the file the data data file back into carrie's folder here now from kerry's perspective she has a data file in her photo path of course she doesn't have this particular photo path doesn't have an eden photo path and let's go back and pretend that now she's got that analysis file back and she refreshes it so kerry sees this file you know that it's from ian cause he still has that photo path there but when she goes to refresh that table she's gonna get that error but all she needs to do is go under this path here and just change that to carry click close and load now we just need to refresh this and she's going to get the table so there's no errors there and now they can do this back and forth with this particular file now doing it one file may not be a big deal but if we had several files several data files data one file one data file two data file three and they all had their own transformations and they're all equally as large all you need to do then is just create one parameter and have the source of each one of those look for that that directory path and this makes it a little bit easier to share files when your source is really large and you are collaborating with other power query users so this is the way that we can get around that datasource.error message when we're sharing power query files so i hope that helps thanks for watching you

Channel: Doug H

Views: 45,697

Keywords: dough, microsoft, tips, tricks, tutorial, training, easy, simple, learn, windows, basic, how-to, how, to, how to, chart, charts, graphs, doug h, doughexcel, doug h excel, create, make, excel function, excel formula, excel chart, excel dashboard, excel, powerpoint, data, analyze, advanced, free, formulas, download, online, xls, vlookup, top channel, best excel, power bi, power point, viz, Powerpoint, data viz, lookup, power query, share, error, sharing

Id: gGl_6r2bkAg

Length: 7min 48sec (468 seconds)

Published: Sun Aug 29 2021