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

Рет қаралды 15,877

2 жыл бұрын

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

Пікірлер: 33

@munidulam6212 2 жыл бұрын

@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 2 жыл бұрын

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

@marvinalavi7620 2 жыл бұрын

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

@seamushand8439 2 жыл бұрын

Really useful and clear explanation. Thank you

@sianthuraisingam9216 2 жыл бұрын

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

@linustorvalds306 2 жыл бұрын

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

@RADACAD 2 жыл бұрын

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

@saketsrivastava84 2 жыл бұрын

Fabulous video

@shivmantra1406 2 жыл бұрын

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

@RADACAD 2 жыл бұрын

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

@dareemmanuel6079 4 ай бұрын

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

@mladenradosevic5333 Жыл бұрын

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

@DanielWeikert 2 жыл бұрын

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!

@shaibeezz 2 жыл бұрын

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.

@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?

@vladimirmihaylov1844 2 жыл бұрын

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?

@AliHoolash 2 жыл бұрын

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 2 жыл бұрын

Yes this should work. best regards

@RADACAD 2 жыл бұрын

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

@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

@unick_gul7538 2 жыл бұрын

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

@chaitubhogireddy 2 жыл бұрын

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. '.

@smartwork4768 2 жыл бұрын

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.

@irfanpukhta9959 Жыл бұрын

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

@harshitdoshi2004 2 жыл бұрын

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

@RADACAD 2 жыл бұрын

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

@harshitdoshi2004 2 жыл бұрын

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

@RADACAD 2 жыл бұрын

@@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

@hlo0o0o0le Жыл бұрын

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

@arkaprovasaha4313 11 ай бұрын

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

@arkaprovasaha4313 11 ай бұрын

And I want to change the parameter value using slicer

@RADACAD 11 ай бұрын

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.