Hello everyone today we are going to learn how you can dynamically change the data source using the query parameter. So I already have a video on query parameter and people like it so thanks for that and I have a comment from the viewer that if it if it is possible to change the data source using the query parameter. So I know that within the same data source you can switch the file using the query parameter however by using M query language and query parameter you can overcome this situation as well that you can have two identical file on two different data sources and you can switch them dynamically so without a delay let's get started I'm going to open up Power BI and my file is in Excel and in SQL server so I'm going to open up a SQL server database first and my server name is here and I'm going to click on OK and I'm going to use the AdventureWorks DW2019 and my data table is Dimri seller table. Okay, and I'm going to click on transform data and here we are in Power Query and My database table is here as you can see here and left bottom corner that 22 columns and 701 rows it has okay. So now we are going to get the data file from the excel So from the new source and excel workbook And here is my DIMM Reseller file and it has a DIMM Reseller worksheet.
So I'm going to click on OK. OK, so my first file DIMM Reseller is when I click on this Advanced Editor from the Home tab and Query section. I'm going to click on Advanced Editor and we have this three line code and how we get that file from the database.
OK. Now if you do not know your to read mQuery code then I have a video I'm going to link that video in the description you can go ahead and watch that so you can understand easily. Now I'm going to copy these three sentence between let and in so control C and I'm going to click on cancel and I have now this dim reseller second table which has a 574 rows which is from the Excel and if I click on advanced editor then I have this code that how power query got this file from the Excel okay so I'm going to just click on this my first line where the source word start and if I enter it I have a space now underneath the let so I'm going to paste my code from which I got it from the database file source and I'm going to apply my comma because in mQuery every sentence at the end you have a comma except just last sentence so that now we have here now two same variables which is not possible source and source so I'm going to change in my data source or the sorry the SQL server code I'm going to change it with SQL and underscore at the start of the source so that is my I'm changing my variable name here and then in a second line as well the code continue using the source so I'm going to change that as well with the new name SQL underscore source okay so now there is no repeat of the source there is only one variable naming source other variable naming SQL source and then at the end we have after in you return last variable so because this file is belongs to the excel code so it the last at that time was this Change type variable and that's why it has in return change type variable So we will look at that in a minute for now. I am going to click on done now we will create our parameter so I'm going to click on this manage parameter and click on this new parameter and my parameter name is my data source so or we can give a description like select data source then I'm going to leave this required as it is in the type we are going to choose text because our naming of the data sources will be in text and then in suggested values I'm going to use a list of values because we have two different data sources so my first is Excel and my second data source is SQL server so I'm going to use a SQL underscore server so that is my second database And then we need to give this default value one out of these two. So I'm going to choose Excel.
You can choose anything. Okay. Anything is available. And current value as well Excel. Now all this you can learn from that video which I created earlier on query parameters.
And I'm going to leave the link in the description. And I'm going to click on OK now. So by default it's Excel but you can change it to the SQL Server as well.
Now let's go back to our our advanced editor where we made a change. So the first three lines are belongs to our SQL Server database m code and remaining four lines are related to the Excel file. So now at the last line just before in at the end I'm going to add a comma because I wanted to add a logical statement here so comma and then enter and my as you know we have to declare a variable here so my variable name here is result is equal to if so if is our of the m function remember that M is a case sensitive language so you have to use accordingly now if now our parameter name is my data source so as soon as you type starting my you have this option my data source is equal to and then in double quote our option is Excel so Excel then then what if we have a xl then in the xl code our last variable is this change type right and that that we are returning as well so that and or else so if else so if my data source is equal to xl then return change type okay else return this variable because that is our SQL Server database code okay so there are last variable is DBO dim reseller okay and then we need to change here to return our result variable right so that's the way we and as soon as you get the result you have here because that's the way you are going to return So now our according to our choice of the parameter this logical statement is going to choose what should be the response which code should execute this or this and we are going to return that using our result variable okay and there is no syntax error have been detected and I'm going to click on done.
So that is done now. Our code is here. Now you can delete this which is our SQL Server database file. We can delete it because we already have that code in our second file. Okay and I'm going to rename this as well because we don't have two files now so dim reseller.
Okay so So now we have one parameter using our two data source option and we have both data source code in our Advanced editor and we applied our Logical statement. Okay here. Okay, so let's apply and close and Try that in Power Power bi desktop. Okay. So now we have dim reseller Data ready and our default option is Excel.
So I'm going to use one card here so we can see the results and I'm going to just click on any field for example resale or name and I'm going to use that from this file. I'm going to use the count of it. Okay so we can see the aggregation should be the count.
Okay, so 574 rows we have in our Excel file where let me increase the fonts so you can see it. I believe you can see it. So that close that close leave it that. And now we are going to use our from the transform data.
We are going to edit the parameter here. So remember that our SQL Server file has a 701 rows. So I'm expecting here 701 rows. So okay and apply changes and we have our 701 rows.
So this file identical file is belongs to the to the SQL Server. if I go back and edit my parameter to my Excel then it should be 574 rows supply changes and we have over 574 rows so guys this is all about this question that can I switch my data source itself using the query parameter then the answer is yes and you have this video Use this feature and if you already using this then let me know in a comment. And thanks for watching. If you like the content of the video then please comment, like, share and subscribe my channel.
And thank you for watching.