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

Подписаться 47 тыс.

Просмотров 16 тыс.

50% 1

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

Наука

Опубликовано:

13 сен 2021

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 33

@marvinalavi7620 2 года назад

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

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

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

@sianthuraisingam9216 2 года назад

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

@seamushand8439 2 года назад

Really useful and clear explanation. Thank you

@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

@dareemmanuel6079 5 месяцев назад

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

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

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

@mladenradosevic5333 Год назад

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

@hlo0o0o0le Год назад

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

@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 2 года назад

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?

@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

@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

@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

@irfanpukhta9959 Год назад

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

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

@unick_gul7538 2 года назад

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

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