Change parameter of data source in Advanced Editor

[Anonymous]

Hello guys,

In my project, we have 2 environments Development and Production which should have same data tables with same columns, only data change. These environments are in 2 differents instances of SQL SERVER thus once the development of the PIBX is done, I will have to change the data source parameters to link to the PROD( switch from the Dev environment to the Prod environment). It avoid to do all data transformation again

For that, I have done a test : create a same table in these environments and a PIBX which is linked firstly to the Dev with some data transformation ( head-column name changed & data type changed). After, I opened the Advanced Editor in Power Query to change parameters of the data source like below :

-------------------------------------------------------------------------------------

//DEV

 let
Source = Sql.Databases("DEV"),
dev_instance = Source{[Name="dev_instance"]}[Data],
dbo_Table_Test_T = dev_instance{[Schema="dbo",Item="Table_Test_T"]}[Data],
#"Type modified" = Table.TransformColumnTypes(dbo_Table_Test_T,{{"Date_1", type date}, {"Date_2", type date}}),
// Change DR - Unity
// Date_1 - Start Date
// Date_2 - End Date
//
#"Column renamed" = Table.RenameColumns(#"Type modified",{{"Date_1", "Start Date"}, {"Date_2", "End Date"}, {"DR", "Unity"}})
in
#"Column renamed"
-------------------------------------------------------------------------------------
To link the PROD :

-------------------------------------------------------------------------------------

//PROD

let
Source = Sql.Databases("PROD"),
prod_instance = Source{[Name="prod_instance"]}[Data],
dbo_Table_Test_T = prod_instance{[Schema="dbo",Item="Table_Test_T"]}[Data],
#"Type modified" = Table.TransformColumnTypes(dbo_Table_Test_T,{{"Date_1", type date}, {"Date_2", type date}}),
// Change DR - Unity
// Date_1 - Start Date
// Date_2 - End Date
//
#"Column renamed" = Table.RenameColumns(#"Type modified",{{"Date_1", "Start Date"}, {"Date_2", "End Date"}, {"DR", "Unity"}})
in
#"Column renamed"

-------------------------------------------------------------------------------------

After this change, all the data transformation still have worked because the name of the data table and the name of columns are the same between the environments. Mission accomplished however, it is the first time for me so I don't know if this way is a good pratice.

What do you think ? Do you have some advices,recommandations to improve? Do I need to use parameters ?

Thank you very much and have a good day !

[mohammedadnant]

Hi @Anonymous 

Good day,

That is the normal method and it is fine, few other methods are

1. Use parameters as you said

2. You can use Data Source settings (if all the table are from same Dev to Prod)

here you can change the data source settings and it will change to all the tables in 1 go

3. if you have premium or premium per user licence you can use deployment pipeline, refer this video

https://youtu.be/XtEMSmr1TEY

Thanks & Regards,

Mohammed Adnan

[Anonymous]

do it work with Power BI Pro ?

[amitchandak]

@Anonymous , You can use parameter

refer: https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters

video from guyinacude

https://www.youtube.com/watch?v=XIq5vN5oPf8

[Anonymous]

Hi

thank you for the solution !

I have Power BI RS and I found the behavior is really strange.

I think the parameter works on Server field but not on the Database field. When I change the value of the parameter for the server, the power query update it but it's not the case for the database. For the database, I noticed that the Power Query take the name of the parameter to put it in the information of the data source instead of taking its VALUE. So to make it works, I changed directly the name of the parameter so its value is useless

It's because of the version of Power BI ?