Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Medium
Question 44
A Power BI Data Analyst needs to update a report’s data source from a development SQL Server database to a production SQL Server database. The table structures are identical, but the server and database names differ. The analyst wants to make this change with minimal effort using Power Query Editor.
What should the analyst do?
-
A
Use DAX to create a calculated table that references the production database instead of the development database.
-
B
In Power Query Editor, update the Source step of the query to point to the production SQL Server database and refresh the data.
-
C
Edit the dataset in Power BI Desktop’s Data view to update the server and database names.
-
D
Create a new query in Power Query Editor for the production database and replace the existing query.
-
E
Export the data to CSV, update the source file path in Power Query Editor, and import the production data.
Reveal correct answer
Correct answer: B
Explanation
Problem Breakdown
The analyst needs to update a report’s data source from a development SQL Server database to a production SQL Server database. The requirements are:
Table structures are identical (same columns and schema).
Server and database names differ.
Minimize effort using Power Query Editor.
Domain: Prepare the Data
Why This Configuration?
Update Source Step in Power Query Editor:
In Power Query Editor, the Source step of a query defines the connection to the data source (e.g., SQL Server database). Since the table structures are identical, updating the server and database names in the Source step is the most efficient way to switch to the production database.
This approach requires minimal changes, as it preserves all subsequent query steps (e.g., transformations, filters) and avoids recreating the query.
Refresh the Data:
After updating the Source step, refreshing the query retrieves data from the production database, ensuring the report uses the new data without altering its structure or logic.
How It Works
In Power BI Desktop, open Power Query Editor (Home > Transform Data).
Select the query connected to the development SQL Server database.
Locate the Source step in the Applied Steps pane (typically the first step).
Edit the Source step’s formula or settings:
Update the server name (e.g., from dev-server to prod-server).
Update the database name (e.g., from DevDB to ProdDB).
Example: Change Sql.Database("dev-server", "DevDB") to Sql.Database("prod-server", "ProdDB").
Update credentials if needed (e.g., for the production server) via Data Source Settings.
Click Close & Apply to refresh the data from the production database.
Verify the report visuals update correctly and publish to the Power BI Service if needed.
Why Not the Other Options?
Create a New Query for the Production Database:
Creating a new query and replacing the existing one requires reapplying all transformations, which is time-consuming and error-prone compared to updating the Source step.
Edit Dataset in Data View:
Data view in Power BI Desktop is for viewing and editing data, not for changing source connections, which is managed in Power Query Editor.
Export to CSV and Update File Path:
Exporting to CSV and importing into Power BI is manual, breaks the live connection to the SQL Server, and doesn’t leverage the identical table structure.
Use DAX to Create a Calculated Table:
DAX cannot create tables that connect to a new data source like a production database. DAX operates on data already loaded into the model, not on source connections.
Practical Considerations
Credentials: Ensure the analyst has valid credentials for the production SQL Server and updates them in Power BI’s Data Source Settings.
Query Steps: Verify that subsequent query steps (e.g., filters, joins) remain compatible after the source change, as column names and data types are identical.
Testing: Test the query refresh in Power Query Editor to confirm connectivity to the production database.
Power BI Service: If the report is published, update the data source credentials in the Power BI Service dataset settings to ensure scheduled refreshes work.
References:
A.
DAX is used for calculations, not for changing data source connections, which is a Power Query task.
B.
In Power Query Editor, the Source step of a query specifies the database connection. Updating the server and database names in this step (e.g., in the SQL Server connection string) changes the data source with minimal effort, as the query steps remain unchanged due to identical table structures.
C.
Data view in Power BI Desktop is for viewing data, not editing source connections, which are managed in Power Query Editor.
D.
Creating a new query is unnecessary and requires reapplying all transformation steps, increasing effort compared to updating the existing query’s Source step.
E.
Exporting to CSV and re-importing is inefficient and unnecessary when Power Query can directly update the database connection.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
