Change the Data Source TYPE of the Power BI Dynamically using Power Query Parameters

I have written a blog article and explained how Power Query parameters can be used to change the data source in Power BI. Another useful scenario of it is actually to change even the data source itself. For example, you may have two data sources with exactly the same data structure (columns), but different data. One of them is Excel, and another is the SQL Server database table. In this video, I show you how you can switch between these without needing to change your code and publish again. All of that using Power Query parameters.
Find the links to study more in my blog article below:
radacad.com/change-the-data-s...
*******************
Want to learn about Power BI or AI? or take your Power BI and AI knowledge to the next level for building reports? Check out our courses in RADACAD Academy for all aspects of Power BI and AI.
RADACAD courses: radacad.academy
Our upcoming courses: learn.radacad.com/events/
Free books and tools for Power BI: radacad.com
Power BI Helper: powerbihelper.org/
Become an academy member: learn.radacad.com/academy/
*******************
********
CONNECT with US!
********
-- / radacad_com
-- / rad_reza
-- / leila_etaati
-- / radacad
-- / leila-etaati-b0304636
-- / rezarad
-- / 6393399
-- radacad.com
********
#PowerBI #PowerBIfromRookieToRockStar #PowerQuery #DAX #Visualization #RADACAD

[@munidulam6212]

@RADACAD, Your way of teaching is excellent. This video is really useful to me. Infact, not only this video, all your videos are useful. I'm thankful to you!

[@RADACAD]

I am glad we could help, thanks for your kind words :)

[@marvinalavi7620]

Thank you Reza, that saved me 20 days of time!

[@seamushand8439]

Really useful and clear explanation. Thank you

[@shivmantra1406]

Rezza, this video doesn't deserve 100+ likes. It deserves a million+ or more likes. Thank you for this video

[@RADACAD]

Thanks for your kind words. I am glad it helps!

[@sianthuraisingam9216]

Thanks this is really helpful and will save a lot of manual rework to rebuild with different data source type

[@linustorvalds306]

Very useful, I did something similar to point to a local folder vs a Sharepoint, great tutorial.

[@RADACAD]

Thanks Pepe. It was great to hear the scenario you used this approach for.

[@mladenradosevic5333]

Great stuff. Any thoughts how to stop refreshing if datasource is missing? Custom Error message or just do not refresh...

[@dareemmanuel6079]

Thanks for the tutorial. Where do I get the links you keep mentioning because I don't see them in the description like you stated. Thanks

[@hirdeshgupta3724]

Thanks, two questions 1. Can we just replace excel to DB query so it will point to SQL? 2. Can I do same for Merge query?

[@DanielWeikert]

Question would be how to offer this feature to an end user. He/She is not able to click edit queries and there is no way to link a parameter from the report to the backend powerquery parameter or is there? Highly appreciate your input here!

[@saketsrivastava84]

Fabulous video

[@mokshi02]

Thanks @radacad I have one qn What if we had to add three or more data source what is the query for that? Can u pls explain

[@vladimirmihaylov1844]

It is worth pointing out that parameter options are not available for Power BI reports deployed to Power BI Report Server. It will be good to know, how to achieve the same in such cases?

[@shaibeezz]

Hi, i am facing one issue in Power BI Desktop V Nov2021- I need to connect the two different types of files (xlsx and HTML) at the same from the same folder. both types are having the same structure (number of columns and header). The current type is xlsx is connected and now I have to add the HTML type in the same folder. for this, I have created a parameter? as data will continue in both files. appreciate your help in advance.

[@AliHoolash]

Than you for this nice tutorial, Reza! I am facing a similar situation right now where I need to update the data source from a Power BI Report from Excel to SQL Server. What if I don't want to keep that Excel data source and work only with the SQL Server data source? Can I simply overwrite the M codes in "Fact Internet Sales" with those from "Fact Internet Sales (2) (and thus get away with the need to create a new Parameter)? Would this approach still maintain the existing relationships among the tables?

[@danwe5520]

Yes this should work. best regards

[@RADACAD]

That is correct. you won't need parameter and IF statments

[@unick_gul7538]

Can we use parameters for the data source of snowflake in power bi?

[@smartwork4768]

Hello Genius, I never miss your any of the videos and you are absolutely amazing in this. Well, i have one query if you could guide me here, - Let's say i have load query from folder and i have multiple files available in that folder belongs to every month sales and let's say i have built one report and loaded in excel using power query. - Now after deleting a few files from folder, how could i keep old file data in excel where i have loaded the query besides that file get removed from folder? - as we know after refracing of query it would load fresh data only in model. basically, i want to preserve old data also in my excel where i have load the query. Thanking you in advance.

[@hlo0o0o0le]

I tried to connect it with google drive but didn’t work for me , it was showing me error in displaying visuals

[@irfanpukhta9959]

Can I pass the datasource as a parameter in the URL of the published report?

[@harshitdoshi2004]

This is excellent. Can you also change the connection type from Import to Direct Query?

[@RADACAD]

Unfortunately that won't be possible using parameters. you need to change some specifics of the Power BI file for that.

[@harshitdoshi2004]

@@RADACAD Can you elaborate on what specifics do I need to change?

[@RADACAD]

@@harshitdoshi2004 DirectQuery and Import data are two different methods of connecting to the data source. You can re-build your model by getting data from the source using DQ, but then copy and paste visuals into the new report

[@chaitubhogireddy]

This approach work for direct query ? im seeing this error Failed to save modifications to the server. Error returned: 'DirectQuery partition 'DimProduct-f5c7e8b6-9037-4b5e-bc93-9ed1fe9f39fe' has '2' datasource reference(s) in its expression which is not allowed. '.

[@arkaprovasaha4313]

Can we do the same for azure databricks in a similar way?

[@arkaprovasaha4313]

And I want to change the parameter value using slicer

[@RADACAD]

You can use this method with any type of data source. however, changing the parameter value using slicer won't be the best experience, because even if you implement it this way, the user has to REFRESH the PBIX file after the change in the slicer. Power Query parameters aren't meant to be changed through UI. I have a video about the difference of Power Query parameters and Power BI DAX parameters in this channel. worth to watch.

[@ravichandra183]

Keep id data source link to practice