10. Parameters in Power Query (and Other Kinds of Parameters)
10.4 Parameterising Server and Database (Dev → Prod)
Steps in Power BI
- Create
ServerName(Text, List of values: QCTEST01, QCSERVER01; current QCTEST01) andDatabaseName(Text: QuickCommerceDB). - Open Advanced Editor for each SQL query and replace the hard-coded names (code below).
- Build and test the report against QCTEST01.
- Before publishing, switch
ServerNameto QCSERVER01 (Edit parameters) › refresh › publish. - In the Service, open the semantic model Settings › Parameters to change values there too. The gateway must have a connection for each server you use (Module 4.8).
Source = Sql.Database(ServerName, DatabaseName),
Orders = Source{[Schema = "dbo", Item = "Orders"]}[Data]
Ravindra Bagale's Tip
Remember one thing: the mistake I see is changing server names by editing every query by hand when moving from Dev to Prod. Use one ServerName and one DatabaseName parameter for all queries. In the Service, change them in the semantic model settings or with deployment rules. Remember this rule.
Ravindra Bagale's Tip – मराठी
एक गोष्ट लक्षात ठेवा: मला दिसणारी चूक म्हणजे Dev वरून Prod ला जाताना प्रत्येक query हाताने edit करून server names बदलणं. सगळ्या queries साठी एकच ServerName आणि एकच DatabaseName parameter वापरा. Service मध्ये ते semantic model settings मध्ये किंवा deployment rules ने बदला. हा नियम लक्षात ठेवा.
Ravindra Bagale's Tip – हिंदी
एक बात याद रखो: जो गलती मैं देखता हूँ वह है Dev से Prod पर जाते समय हर query को हाथ से edit करके server names बदलना. सभी queries के लिए एक ही ServerName और एक ही DatabaseName parameter इस्तेमाल करो. Service में इन्हें semantic model settings में या deployment rules से बदलो. यह नियम याद रखो.