Making data source parameters easy in Power BI Desktop

Maaaaan... I had so many deadend Google searches and your 2-minute Tuesday tip from 4 years ago had the key! Thank you!

A question: Can someone explain - why would you choose to move between different environments? And toggle between different parametere?

Second: why Call Them server and database?

How do you add date parameters to Azure Devops data source using power bi ?

Can you please tell us how to do this in automatic way?

thanks so much

How to parameterize the table names. I want a parameter to decide which table to load but the tables structure will be different. How do we achieve this?

Patrick, I have a parameter that has 3 different folders, this so i can change for testing of data before going live. For some crazy reason, one of the folders (sharepoint) I can access using the url, wont load. Says table empty, have checked .xlsx files all there, I switch back to another folder (identical files, but test data) and works .. any ideas?

Hi, I want to parameterize the database, server, table names from an azure databricks database, but at the same time I want the parameter values to be the table names I click on the powerbi report, in summary can I parameterize the values I'm clicking in on a report ? any suggestions will be appreciated.

Can this be done with data sources?

love your energy

Hi Patrick, I am not using SQL server and am using ODBC driver to connect to Mongo Atlas. How can I easily switch the data source to a new DSN? QA to production. Many thanks

Question: Is there a way to have end-users enter Power BI query parameters or a work around for it? I am currently using SSRS but it is very limiting compared to BI. For example, I work for an international manufacturing company with a list of ~250k materials, I would like users to enter the material number so I can pull up data through multiple queries that will run very fast on one material but cannot possibly run on all the materials. The data can then be summarized with drill downs for historical use, future demand, current purchase orders, current inventory and locations, etc. Currently this not an option. Very frustrating.

Nice. Is there any way to prompt the user to change a parameter? Im really stuck at this. I want the user to be able to change the parameter, which I am using in my data model. Is there any way?

Never notice that check man, thanks a lot

I also make use of this parameter but after that it's not allowing for native query to use

Hello very nice video as always thank you Patrick.. It seems to be working only for SQL server connector right.. Do we have any visibility if that will be expanded into other connectors like databricks for example?
Many thanks,

Hi Patrick, Need your help.
Actually I want to know, how we can pass a dynamic parameter into the API's so that we can change our API's data according to that parameter.
Problem is that I have 1000 databases in my task and these databases data only be accessible through the API's and I have to create the dashboard which is going to be used by these all 1000 clients databases individually.

Very good, this was a requirement in our project ,

Thank you so much for this video. I really got stuck in parameterized connection and this video helps my issue in just a few minutes.
Looking more interesting video like this

Does anyone know how to use this function with a databricks data source?

Hey Patrick! Can you make a similar video but with using Dataflows as your source? That seems to be giving me a headache, as I want to swap between Test & Prod when using Deployment Pipelines and Dataflows.

For anyone wondering: it seems like the option "always allow" under the ribbon view is moved to Options and settings -> Options -> Global / Power Query Editor. Here you can tick the box that says "Always allow parameterization in data source and transformation dialogs".

Patrick the next level issue how to config different datasource db Gateway in services

it is very helpful indeed. now i got the tricks also

Can't we do this if the souce is Databricks as am not getting the option to add parameters when choosing the source as Azure Databricks or Spark?

I love finding a video from three years ago that gives me exactly what I needed. Amazing, thank you.

redo this video for 2022

Hey What if I want to add parameters to my SQL statement

👍👍👍

Gosh that Energy! Great video! quick and to the point!

Would it refresh the existing source as well. I mean in case of adding any new column in the data source

Can you parameterize the table too?

Patrick, You solved half of my first problem with this video. The second half of my first problem is to dynamically name a table and then use the data in it. I have a series of tables in Azure hosted SQL Server, Student2018, Student2019, Student2020, etc. That is a snapshot of each years student body. I need to be able to allow the user to set a parameter for the year, and open that year's table. We have several tables like this. All the tables have the same structure. I've been doing this in VBScript with no problems. Thanks in advance for the help.

please advise, Is this parameter option is available for Snowflake source?

How to change power query's source of data through VBA?

Hello Patrick, this is amazing and very very useful to easily switch to different envioroments, does this work for Direct Query as well?

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

Cool! Is there any way to do the same for schemas? In the company I work, we have different tables with the same names but in different schemas. It'd be great if we could parameterize this as well, in order to change only the name of the schema.

Dude, this is bananas! This saves a lot of time in our setup, thanks so much!

Great! Exactly what I was looking for a client related query :)

Love your videos Patrick. Does this parameterised data source work with Gateway connection for you? I am not able to select (greyed out) Gateway connection at the power BI service when the data source is parameterised. Hence getting refresh error as need to connect to Gateway.

Great tip and channel! Can you do a video on using parameters as part of the dataset refreshing. This way locally we can develop against one database and then the published report can go against a different database.

That was amazing, thanks Sir Patrick for all your tips.

Hey! I love this. So simple when you know how. One question. I just followed these steps but I use the odbc source rather than SQL server due to my organisation but the drop down does not allow parameters in the same way as SQL server. Is this expected? Is this option only suitable for SQL server sources or can something similar be applied for odbc connections? Thanks!

Life saver! thank you

Amazing and thanks for sharing!

Just too damm good mate.. loved it.. Thanks a mil.. appreciated !!

As always Patrick is great!

I think that Microsoft have changed this (do they do that a lot or is it just me?). I couldn't find this option in the ribbon anywhere but if you go to File -> Options & Settings -> Options -> Power Query Editor -> Under Parameters tick the box to Always Allow Parameterisation in data source and transformation dialogs, that will ensure that these parameter options are then available.

Thanks Patrick, your videos are so useful and well explained and have helped me solve many problems. I just have a question for you, is there a way to bind parameters created when the import of data is from an Excel spreadsheet? I import the data, successfully create the parameters and embed them in the M query, but when I try to bind them to a table created by using DAX the binding parameter option is not available. I know they mention that this is only for direct queries but wanted to know if you knew a way around.
Thank you!!!