Change Data Source Dynamically With The Help Of Parameters In Power BI
Introduction:
This blog post provides how to utilize parameters and M-Query to dynamically switch the data source (For example, switching from Snowflake to SQL Server).
Step 1: Install Power BI desktop using the given link (Downloads | Microsoft Power BI)
Step 2: Open Power BI Desktop.
Step 3: Click on Transform Data in Home menu.
Step 4: Create parameters based on the requirement. For now, just adding two parameters named Snowflake and SQL Server as list type of values.
Step 5: After creating the parameter, create an empty table without entering any data and rename the table, based on the requirement.
Step 6: Click on the table that was created. In transform data under Home tab → Advance Editor Options. Click on Advance Editor.
Step 7: Pop-up will be shown as like below image and provide the custom M-Query in the advanced editor.
For Example, assume below credentials.
Snowflake:
Server: “abcde-sk235678.snowflakecomputing.com”
Warehouse: “SALES_WH”
Database: “DATA_MART”
Schema: “DEVELOPMENT”
Table: “FACT_SALES_2023(1)”
SQL Server:
Server: “mysql-db- ap-south.amazonaws.com”
Database: “EMPLOYEE_SALES_02”
Schema: “PRODUCTION”
Table: “FACT_SALES_2023(2)”
The logic behind the syntax is passing the parameters in IF ELSE condition. If the selected parameter satisfies the condition, then entire M-Query (Data source) will execute based on the provided credentials.
Step 8: After providing the credentials for above syntax, the advanced editor will show “No syntax errors have been detected” as like below image.
Step 9: Click “Done” in the advanced editor. Then Click on “Close & Apply”. The window will be navigated to visualization page.
Step 10: After completing all the above steps it will be navigated to below page and in the same transform data there will be another option called “Edit parameters”, click on it.
Step 11: A pop-up will be displayed as like below image and there is an option to change the parameters.
After changing the parameter, the data source will be changed dynamically according to the parameter fields.
Conclusion:
This step-by-step guide to improve your Power BI experience. It gives you the flexibility of switching between different data sources with ease, which is beneficial for both the development and production stages.
About Us
Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe, and we’re quickly building a brand that is well-known for superior delivery.
Website: https://bi3technologies.com/
Follow us on,
LinkedIn: https://www.linkedin.com/company/bi3technologies
Instagram: https://www.instagram.com/bi3technologies/
Twitter: https://twitter.com/Bi3Technologies
