So in this video I'm gonna show you how you can switch from one data source type to another easily.
So let's say for example that you are connected to maybe some CSV or some XLS files
right, but you know that was just a temporary thing and finally you got access to the SQL server
That's where the files were being generated from. Maybe somebody else was dumping it for you
and now you have direct access to that SQL server. Great. More power to you my friend, right.
But now what you would want to do is you would want to switch your Power BI model
easily without having to redo a lot of work from using these CSV or XLS files to directly using the SQL server. Now
Of course
Now what I'm going to show you and the scenario could apply when switching from any data source to any other data source.
So let's take a look. So first thing I'm gonna show you two wrong ways of doing it.
and then we're going to show you the right way of doing it. Alright, so let's get into our query editor here.
Alright so it's coming up. So the first thing that users look for usually so in this case if you notice that our
These are connected to xlsx and you can you can look at it other ways. Sometimes I open the query dependencies view
I love that visual view
And it's you know, it's the colors a little wonky
I'm not sure why but yeah, you can hover over to see and that is coming from an excel file and so forth
But often what people do is they would go to
Their data source settings and look for something there now. It does seem promising when you open data source settings
It's like oh cool
I want to change this and there's this change source button right there, but it it's not what you think it is
So in here well, all you can do is pretty much, you know, scrap change data source
It can still be an Excel X X has X file, but you can if the location changed then you can change it
Alright, but this is this is not gonna let you switch to a sequel server
So for that there is another wrong way to do it and let me demonstrate that
But it that that one is going to really close and there's just one small thing. They need to switch
So in this case right again, so this all of these tables are coming from excel file
you can see I have my whole model built and if you quickly will switch over back to this then I have my
you know measures defined and all that sort of stuff and all my graphs define, right so
It's it's good life is good
Right, and and I just want to switch from one source to another
So what you might do some users do this where they say cool. I'm gonna bring in the new table
So I'm connecting to my sequel server here and that asks me a few questions
There we go. So I'm just gonna say
for the server name and hit OK and
and of course
In my case, it should pop up and asking me about the location of the file
which is right here and I'm gonna pick my sales table, which is right here and I'm gonna hit OK and
There so now I have this table from the the sequel data source
And this is where people go wrong and I say cool now
I don't don't need this guy
Right, so I don't need this guy and because I have this one here
I'm just gonna delete that one and rename this one to sales. So they might delete sales and
Let's say oh cool now I can just come back here and click sales and you know
The the column names and everything is exactly the same. The column names are the same the data types are the same
Looks like we're good to go
Well, not quite let's see what happens. So once you hit close and apply
And it's gonna load the table and well, okay, let's just wait for that Big Finish
Boom yeah, so you saw it just broke about
everything and it's like oh
Well, the good news is that you've really close. So let me redo this and show you the right way to do it
Alright, so I'm back. Alright, so I
Undid a few steps and so we're back
Before the critical step, right?
so will we
Have here is this table is coming from sequel and this is an old table connected to that right so you can see
This one is connecting to my sequel database and this one is sales
so the trick here so of course first thing you need to make sure is that
Column names and the datatypes and stuff it matches, right? So everything just matches
Right. So but what you're going to do is I want you to go to advanced editor and
Copy this whole thing and then go back and and again, right you can't delete this table, right? So
This is that what's connected to probably I knows that oh, that's the one that's loading here
It has the measures inside to find all that stuff. So you can't delete the table and just rename this one
You saw what that did, but what you can do is totally change the guts of this like so, you know
And of course, you know, so let me just show you so you're gonna go to advanced editor and this these are the guts
right
this is what's
Connecting to excel and so forth and you can go in here and you can make any change you want and of course the change
We're going to do is the the M code that we have copied from here. We're just gonna paste it here
So now it's pointing to the sequel server
But it this is the same table it is it is the same table and I might have to rename
This to sales list. Let's try it. See what it does. Oh
It keeps it right. So yeah, you can just copy paste it deep kept that name
so again, you connect to this sequel set and then you
you know go to the advanced editor to copy that code and just paste it in here and
Now sales, and you can see in the query dependencies view as well the sales table
I apologize some the colors are a little wonky, but you can see here
So this is my sequel sequel table and sales is now connected to this and that's it. That's all I want
Now, of course you realize that you don't really need this table now
We're done with this because the sales essentially is a copy of that so we can delete this and in this case
I only swapped this way one table. Of course, if you have other tables you would repeat the same exercise for them and
And at the end of it you're gonna head hit
Close and apply and again because we didn't like delete it or mess with it in any way we change the guts how it's connecting
Barbie I is gonna be none the wiser, right?
I mean, you know, it's like I don't know. It's the same table same field names and everything, right?
but so yeah, the load is gonna
Not gonna be disruptive notice that it's refreshing now and yep, none offer
Graphs or measures that anything was impact and all of the measures defined are still there
So that is the right way to change your data source
Hey keep watching more videos and deep learning power bi but if you did enjoy this video
I would love to hear from you. So leave a comment like subscribe
All the good stuff while we're on my friends