Dynamically Change Data Sources in Power BI

Oct 10, 2024

Lecture Notes: Dynamically Changing Data Source Using Query Parameters

Introduction

  • Topic: Dynamically changing the data source in Power BI using query parameters.
  • Objective: To learn how to switch between different data sources dynamically.

Background

  • Previous video on query parameters received positive feedback.
  • Viewer query about changing data sources using query parameters.
  • Normally, query parameters allow switching files within the same data source.
  • Solution uses M Query Language to switch between identical files on different data sources.

Tools and Setup

  • Software: Power BI
  • Data Source Examples:
    • Excel file
    • SQL Server database
  • Example Data Table: DimReseller table.

Steps to Change Data Source

1. Opening Data Sources

  • Open SQL Server database:
    • Server: AdventureWorks DW2019
    • Table: DimReseller
  • Open Excel workbook:
    • Worksheet: DimReseller

2. Using the Advanced Editor

  • SQL Server:

    • Copy the code from between let and in in the Advanced Editor.
    • Modify variable names to avoid conflict (e.g., SQL_source).

  • Excel:

    • Use the copied code and integrate the SQL source code.
    • Ensure no variable name conflicts.

3. Creating a Parameter

  • Manage Parameters:
    • Name: MyDataSource
    • Type: Text
    • Suggested Values: List (Excel, SQL_server)
    • Default and Current Value: Excel

4. Implementing Logical Statement

  • Add a logical statement before the in clause:
    • Use conditional if-else to determine the data source.
    • Return the appropriate variable (ChangeType for Excel, DBO_dimReseller for SQL Server).

5. Testing

  • Delete redundant SQL Server table if needed.
  • Rename combined table appropriately.
  • Close, apply changes, and test in Power BI Desktop.

Verification

  • Use a card in Power BI to display row count:
    • Excel: 574 rows
    • SQL Server: 701 rows
  • Verify switching between data sources using the parameter.

Conclusion

  • You can dynamically switch data sources using query parameters in Power BI.
  • Encouragement to try the feature and provide feedback.

Call to Action

  • Like, comment, share, and subscribe for more content.

Detailed steps to follow

Here are the steps to dynamically change the data source using query parameters in Power BI:

1. Open your Power BI Desktop and Connect to your Data Sources:

  • Connect to your SQL Server database:
    • Go to the Home tab and click on Get Data.
    • Select SQL Server as the data source.
    • Enter your server name and click OK.
    • Select the AdventureWorks DW2019 database and the DimReseller table.
    • Click Transform Data to open Power Query Editor.
  • Connect to your Excel file:
    • Go to the Home tab and click on Get Data.
    • Select Excel as the data source.
    • Navigate to your Excel file and select the DimReseller worksheet.
    • Click OK.

2. Access the Advanced Editor:

  • For your SQL Server data source:
    • Click on the Advanced Editor button in the Home tab under the Query section.
    • Copy the code between the lines that start with let and in.
    • Click Cancel to close the Advanced Editor.
  • For your Excel data source:
    • Click on the Advanced Editor button in the Home tab under the Query section.
    • You will see the code for how Power Query is getting data from your Excel file.
    • Click on the line where the word source begins.
    • Press Enter to create a space below the let line.
    • Paste the code you copied from your SQL Server data source.
    • Add a comma after the pasted code.

3. Change Variable Names to Avoid Conflicts:

  • You have two source variables now, which is a conflict.
  • Go to the first line of the SQL Server code.
  • Change source to SQL_source.
  • On the next line of the SQL Server code, change source to SQL_source.
  • Now you have one variable named source for your Excel data and another named SQL_source for your SQL Server data.

4. Create a Parameter:

  • In the Home tab, click Manage Parameters.
  • Click New Parameter.
  • Name: MyDataSource
  • Type: Text
  • Suggested Values: List (Excel, SQL_server)
  • Default Value: Excel
  • Current Value: Excel
  • Click OK.

5. Implement a Logical Statement:

  • Go back to the Advanced Editor for your combined Excel and SQL Server data source.
  • Add a comma after the last line of code before the in line.
  • Press Enter.
  • Type result = if
  • You should see an autocomplete suggestion for MyDataSource.
  • Type MyDataSource = "Excel" then
  • Type ChangeType (this is the last variable for your Excel data source).
  • Type else
  • Type DBO_dimReseller (this is the last variable for your SQL Server data source).
  • Type in
  • Type result (this is your new result variable).

6. Test Your Work:

  • Click Done in the Advanced Editor.
  • Delete the SQL Server table in the query list if you no longer need it.
  • Rename your combined table to something like "DimReseller".
  • Close the Advanced Editor and apply changes.
  • Go back to Power BI Desktop and add a card to show the results.
  • Select DimReseller as your data source and choose any field, such as ResellerName.
  • Choose Count as the aggregation method.
  • You should see a count of 574 rows, which is the number of rows in your Excel data source.
  • Go back to your data source and click Edit Query.
  • Go back to the Manage Parameters section.
  • Change MyDataSource to SQL_server and click Apply Changes.
  • Go back to your Power BI Desktop and refresh your card.
  • You should now see a count of 701 rows, which is the number of rows in your SQL Server data source.

Congratulations! You have successfully created a query that dynamically changes the data source using query parameters in Power BI.