- Yooo! What's up, it's Patrick from Guy in a Cube and I was working on some
stuff in Power Query. This is in the query editor and there were several queries, right? Some were connected to an Excel file some more connects to SQL server. And I needed to change. I wanted to change something
in every one of those queries. And I was like, how would I do it? This is going to take a long time. And then I ran across something that just kind of blew my mind. And that's gonna show you
this video, stay tuned. (upbeat music) If you find this for very first time be sure to hit that subscribe button to stay up-to-date all the videos from both Adam and this guy. Like I said, I was working in Power Query and I had lots of queries. Some from Excel, some from SQL and I needed to change
the connection string. That's what I really needed to change. I wanted to make it dynamic. Instead of using the
hard coded server name or the hard coded path to the Excel file. I wanted to switch it to a parameter. But I had several queries and I was gonna have to do them one by one,
one by one, one by one. And I'm thinking to myself,
Patrick, you're not lazy. You're just efficient. There's gotta be a better way. So I tinkered a bit and
I ran across something. And that's what I want to
show you in this video. All right. So enough of all this talking,
you guys know I like to do. Let's do what? Let's head over to my laptop. Head over to the query editor. So I'm gonna go ahead
and open the query editor and you can see I have
several queries right here. And if you go to the source you can see they're all
pointing to this Excel file. They're all pointing to the Excel file. Or you can open up the advanced editor and you can see they're all
pointing to this Excel file. And what I want to do
is I want to switch them to use this parameter. So if I, if the path
changes, if I make a party a template file, somebody can
quickly update this parameter and it'll automatically load
the data from that location. I thought about a couple of ways to do it. You can go into the
advanced editor, like I said and replace the hard coded
path with the parameter. But again, I have to do that for every one of the
queries, not efficient. And then I said, oh, well I can go right here and click
on the gear and do it here. And I was thinking
again, I have to do that for each one of the
queries, not efficient. And that was wrecking my
brain, wrecking my brain, then I had a thought. Do you need to enable
something before you do this? So I will show you how to enable that. But I have this way that you could do with like a bulk update. Check this out. Before you get into this what
you need to do is you can either go into the query editor, you can click view and you
can choose, always allow. And so this is always allowing the parameterization of
the connection string. Or if you're in the desktop
or the query editor, you can go to file, choose options and settings, select options here in the global section, choose Power Query editor. And you can check this box . Either way it will give
you the same result. So go ahead and click, okay? And then what you do
instead of clicking the gear for each one you go home in the ribbon and you locate the data source settings. You find that data source. So mine in this case, it's an Excel file. And at the bottom, there's
a couple of options, choose change source. Change source because we've enabled "always allow parameterization" you'll have this little drop down. If you don't see that drop down, you need to go enable it, right? So go enable allow parameterizations. Click this little drop down
arrow and choose parameter. And then you can see I have
a CSV file as a parameter but I also have my Excel file. So I'm gonna click the dropdown and choose my Excel file name, click okay and click close. Now it's important that you specify the
path to the file, right? Wherever that Excel file exists. And now if I go to
customer and choose source, you see it's updated. And I was thinking to myself is it going to update all of them? So of course I checked, I went to employee, I
clicked source and boom, it updated it. I went to product, I clicked
source and boom, it updated it. And then I called Adam. I was like, "Adam, dude, dude,
I gotta show you something, I gotta show you something." And he's like, "what, what, what?" So, I showed him and he was like, "what, what the (beep)? Did it change all of them?" I was like, "yeah, it
changed all of them." And you know what he said, "that's gonna make a good video." So that's why he's in this video. But then I thought, will
this work for other sources? So I said, well, let's
try my favorite source. Let's try the old trusty
SQL server, check this out. I have another PBIX file here
where I'm connected to SQL. I'm going to go into the query editor. And so I set this one up
where it's completely broken It's just completely broken. So I'm gonna go manage parameters. I'm gonna create a new parameter. I'm gonna call it my SQL
server, change it to text. And then I'm gonna just put
a SQL server name in here and click okay. And then I'm gonna go over here to data source settings. And there is the data source. I'm gonna say change source. Remember you gotta turn this on, choose parameter, SQL server, click okay, click close. Watch these yellow. Look at them going away. Look at them going away. Look at them going away. Guess what I did. If you call to the source here, you can see instead of the hard coded SQL server name, it's actually the parameter value. So whatever value I put here, as long as there's a
correspondent (mumbles) database with the same schema I may have to provide my credentials, but it's just going to work. That's bananas. Can we say this is insane-amazing. Why? It blew our minds. Have you guys seen this? Did you guys know about this? I'd love to know. Are you doing this a different way? Again, I'd love to know. You guys know how I want to do. Let's continue the conversation. Where? In the comments below. It's your first time visiting
the Guy in the Cube channel, hit that subscribe button, you like a video, gimme a big thumbs up. As always from Adam and myself. Thanks for watching. And we'll see you in the next video.
