Question 1: You launch Power Query Editor and open a query. The percentage of empty values in each column must be rapidly determined. Which Data Preview selection is best?

• Column quality (Correct answer)
• Show whitespace
• Column profile
• Column distribution

Correct answer: Column quality

Column quality in Power Query Editor provides a quick visual summary of each column's data, including the percentage of valid, error, and empty values. This feature is designed for rapid assessment of data completeness and quality directly within the data preview. By enabling it, you can instantly see the percentage of empty cells for every column.

Question 2: You have a list of potential clients with 1,500 rows of data. The following fields are included in the list: - First name - Last name - Email address - State/Region - Phone number The list is imported into Power Query Editor. The list must contain data for every State or Region that you intend to target with a marketing campaign. What are the two things you should do? Each right response offers a piece of the answer. NOTE: There are two correct answers

• Select Column distribution. (Correct answer)
• Select Column profile. (Correct answer)
• Select Column quality.
• Enable Column profiling based on entire dataset.

Correct answer: Select Column distribution.

To identify which states/regions have data and to understand the distribution and completeness of values in the 'State/Region' column, you should use 'Column distribution' and 'Column profile' in Power Query Editor. Column distribution shows the number of distinct and unique values, while Column profile provides a more detailed view, including value distribution and statistics, which helps in identifying missing or underrepresented states. These options offer comprehensive insights into column data.

Question 3: You have a custom connector that gives you the following information for each email sent in the last year: ID, From, To, Subject, Body, and Has Attachments. There are returned more than 10 million records. Based on whom employees send emails to, you create a report that analyzes the internal networks of those employees. You must keep the recipients of the report from viewing the examined emails. The model size must be reduced in the solution. What ought you to do?

• From Model view, set the Subject and Body columns to Hidden.
• Implement row-level security (RLS) so that the report recipients can only see results based on the emails they sent.
• Remove the Subject and Body columns during the import. (Correct answer)
• None of the Above

Correct answer: Remove the Subject and Body columns during the import.

To reduce the model size and prevent report recipients from viewing sensitive email content (Subject and Body), the most effective approach is to remove these columns during the data import process in Power Query. This ensures the data never enters the Power BI model, thus optimizing performance and enforcing data privacy from the outset by not storing unnecessary or sensitive information.

Question 4: You have one of the three Azure SQL databases listed below: - Test - Production - Development You have a dataset that draws its data from the development database. You must set up the dataset so that you can quickly switch between the powerbi.com development, test, and production database servers as the data source. Which one is the best?

• Create a parameter and update the queries to use the parameter.
• Create a query for each database server and hide the development tables.
• Set the data source privacy level to Organizational and use the ReplaceValue Power Query M function. (Correct answer)
• Create a JSON file that contains the database server names. Import the JSON file to the dataset.

Correct answer: Set the data source privacy level to Organizational and use the ReplaceValue Power Query M function.

While parameters are the standard method, using the `ReplaceValue` Power Query M function can dynamically modify the server name string within the `Source` step of the query. This allows for programmatic alteration of the connection details to switch between development, test, and production servers. Setting the data source privacy level to Organizational is a general best practice for allowing data sources to be combined securely within Power BI.

Question 5: You have a CSV file with user complaints in it. The file has a column called Logged in it. Each complaint's log entry includes the date and time that it happened. The information in Logged is formatted as follows: 2018-12-31 08:59. You must be able to use an internal date hierarchy and the logged date to assess the complaints. What ought you to do?

• Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date. (Correct answer)
• Add a conditional column that outputs 2018 if the Logged column starts with 2018 and set the data type of the new column to Whole Number.
• Change the data type of the Logged column to Date.
• Apply a transformation to extract the last 11 characters of the Logged column and set the data type of the new column to Date.

Correct answer: Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date.

To extract the date part from a datetime string like '2018-12-31 08:59' and enable an internal date hierarchy, 'Column by Example' is an efficient Power Query feature. By providing an example like '2018-12-31', Power Query intelligently infers the transformation to extract the date, and then setting the data type to Date ensures it's correctly recognized for date hierarchy analysis.

Question 6: You have a sales transaction database stored in Azure SQL. The database is regularly updated. To find phony transactions, you must create reports from the data. After an update, the data must be accessible within five minutes. How should the data connection be set up?

• Set Data Connectivity mode to Import.
• Add a SQL statement.
• Set Data Connectivity mode to DirectQuery. (Correct answer)
• Set the Command timeout in minutes setting.

Correct answer: Set Data Connectivity mode to DirectQuery.

For data that is regularly updated and needs to be accessible within minutes (near real-time), the DirectQuery connectivity mode is essential. DirectQuery does not import data into the Power BI model but instead queries the data source directly each time a visual is interacted with, ensuring users always see the latest information without waiting for data refreshes. This provides the most up-to-date view.

Question 7: You have a data model with a large number of intricate DAX expressions. The RELATED and RELATEDTABLE functions are frequently mentioned in the expressions. You must offer a suggestion for how to use the RELATED and RELATEDTABLE functions less frequently. What ought to you suggest?

• Merge tables by using Power Query. (Correct answer)
• Transpose.
• Split the model into multiple models.
• Hide unused columns in the model.

Correct answer: Merge tables by using Power Query.

The `RELATED` and `RELATEDTABLE` DAX functions are used to retrieve values from related tables. If these functions are used extensively and causing performance issues, a common optimization is to denormalize the data by merging tables in Power Query. This pre-joins the necessary data, reducing the need for DAX to perform lookups across relationships at query time, thereby simplifying expressions and improving model performance.

Question 8: You are working with a sizable dataset that has more than a million rows. The table contains a Date datetime field. Without sacrificing data access, the data model needs to be shrunk. What ought you to do?

• Divide the Date column into two columns: one with just the time and the other with just the date. (Correct answer)
• Round the Date column's hour to startOfHour.
• The Date column's data type should be changed to Text.
• Cut the Date column out.

Correct answer: Divide the Date column into two columns: one with just the time and the other with just the date.

To shrink a data model with a large datetime column without sacrificing access to both date and time components, it's best to split the single datetime column into two separate columns: one for date and one for time. This reduces the storage footprint because date and time values, when stored separately, often require less memory than a single combined datetime column, especially if the time component has high granularity. This optimizes model size while retaining data granularity.

Question 9: Four sales regions are available. There are several sales managers in each region. In a data model, you apply row-level security (RLS). You give each role access to the necessary distribution lists. You have sales reports that allow for regional analysis. The sales managers have access to their region's sales data. Records from other regions are hidden from the sales managers' view. A sales manager relocates to a new area. You must make sure the sales manager can get the accurate sales information. What should you do?

• The sales manager's Microsoft Power BI license type should be changed.
• Request that the right Azure Active Directory group be added for the sales manager. (Correct answer)
• Edit the reports' Row-Level Security option from Microsoft Power BI Desktop.
• Control the underlying dataset's permissions.

Correct answer: Request that the right Azure Active Directory group be added for the sales manager.

When Row-Level Security (RLS) is implemented using roles assigned to Azure Active Directory (AAD) groups, managing user access becomes centralized. If a sales manager moves to a new region, the most efficient way to update their RLS permissions is to ensure they are a member of the AAD group corresponding to their new region, which is already mapped to the appropriate RLS role. This leverages existing infrastructure for dynamic access control.

Question 10: In a star schema with four tables named Sales, Customer, Date, and Product, you have sales data. Purchase and shipping dates are listed in the Sales table. Although you will most frequently utilize the purchase date, you may also examine the data using both dates separately and jointly. To support the analysis, you must create an imported dataset. The model size and number of queries against the data source must be kept to a minimum. What data modeling approach should you employ?

• Use Microsoft Power BI's Auto Date/Time feature instead of importing the Date table.
• For the date of purchase, create an active relationship between Sales and Date, and for the date of shipment, create an inactive relationship. (Correct answer)
• Use the Power Query duplicate of the Date query and the active relationships between the two Date tables.
• Create active relationships between Sales and both Date tables in the modeling view using a reference query on the Date table in Power Query.

Correct answer: For the date of purchase, create an active relationship between Sales and Date, and for the date of shipment, create an inactive relationship.

To support analysis by both purchase date (most frequent) and shipment date (less frequent, but also needed) while minimizing model size and queries, the best approach is to use one active and one inactive relationship. The active relationship connects the Sales table to the Date table on the purchase date, allowing it to be used by default. The inactive relationship for the shipment date can then be activated in DAX measures using `USERELATIONSHIP` when needed, avoiding the duplication of the Date table and keeping the model lean.

Question 11: A database containing the tables displayed in the following table is used to produce a report for analyzing client transactions. You import the tables. Which relationship should you use to connect the tables?

• one-to-one between Customer and Transaction
• many-to-many between Customer and Transaction
• one-to-many from Transaction to Customer
• one-to-many from Customer to Transaction (Correct answer)

Correct answer: one-to-many from Customer to Transaction

In a typical database design for client transactions, one customer can have multiple transactions, but each transaction belongs to only one customer. Therefore, the Customer table is on the 'one' side and the Transaction table is on the 'many' side. This establishes a one-to-many relationship from Customer to Transaction, ensuring data integrity and allowing for efficient analysis of customer activity.

Question 12: You You have a report with a column chart and a bar chart in it. By client segment, the customer count is displayed as a bar graph. Sales are displayed in a column chart by month. The percentage of the month's total sales that belong to the customer segment must be visible when a segment is selected in the bar chart. When the bar chart is used, how should the visual interactions be configured on the column chart? a map of 500,000 clients using a R visual. In the fields supplied to the visual, you include the CustomerID, Latitude, and Longitude values. Every client ID is distinct. When consumers load the visual on powerbi.com, they only see a portion of the customers. What is the problem's root cause?

• Filter (Correct answer)
• No impact
• Highlight

Correct answer: Filter

When a segment is selected in the bar chart, the column chart needs to display only the sales data relevant to that specific segment, while still showing it as a percentage of the *month's total sales*. The 'Filter' interaction will narrow down the data shown in the column chart to only the selected segment. This allows the percentage calculation to correctly reflect that segment's contribution within each month, providing the desired specific view.

Question 13: You create a report to aid the sales staff in comprehending their performance and the factors that influence sales. The team requires a single visualization to determine the variables that influence success. Which kind of visualization ought you to employ?

• Funnel chart

The correct answer is missing from the provided options. However, to determine variables that influence success in Power BI, the 'Key Influencers' visual is typically employed. This AI-powered visualization analyzes data to identify the factors that drive a specific outcome, making it ideal for understanding what influences sales performance.

Question 14: You are setting up a Microsoft Power BI data model to let users use Q&A to ask inquiries in natural language. The following metric is present in the Customer database that you have. Count of customers = DISTINCTCOUNT (Customer[CustomerID]) Customers are frequently referred to as subscribers by users. By employing Q&A, you must make sure that users may obtain a useful result for "subscription count." The model's size must be kept to a minimum in the solution. What should you do?

• Set the CustomerID column's Summarize
• In the CustomerID field, replace "subscriberID" with a synonym.
• Incorporate a "subscription count" definition into the Customer Count metric.
• A "subscriber" synonym should be added to the Customer table. (Correct answer)

Correct answer: A "subscriber" synonym should be added to the Customer table.

To enable Power BI's Q&A feature to understand 'subscription count' when users refer to 'customer count', adding 'subscriber' as a synonym to the Customer table is the most effective solution. This teaches Q&A that 'subscriber' is an alternative term for 'customer', allowing it to correctly interpret natural language queries. This approach avoids creating new measures or columns, thereby minimizing the model's size.

Question 15: You use the Microsoft Power BI Service to generate a dashboard. A card visual on the dashboard displays the overall sales for the current year. Using the Viewer role on the workspace, you may provide users access to the dashboard. The user requests daily alerts for the number displayed on the card visual. Notifications must be automated. What should you do?

• Create a subscription. (Correct answer)
• Tag the user in a comment.
• Create a data alert.
• Share the dashboard to the user.

Correct answer: Create a subscription.

To provide automated daily alerts for a specific visual on a dashboard, creating a subscription is the appropriate method in Power BI Service. Subscriptions allow users to receive periodic snapshots of reports or dashboards via email. Data alerts are for specific threshold breaches, not for regular daily updates of a value, and sharing or tagging do not provide automated notifications.

Question 16: You have a list of potential clients with 1,500 rows of data. The following fields are included in the list: ✑ First name ✑ Last name ✑ Email address ✑ State/Region ✑ Phone number The list is imported into Power Query Editor. The list must contain data for every State or Region that you intend to target with a marketing campaign. What are the two things you should do? Each right response offers a piece of the answer. NOTE: A point is awarded for each choice that is correct.

• Enable Column profiling based on entire dataset.
• Select Column distribution. (Correct answer)
• Open the Advanced Editor.
• Select Column profile. (Correct answer)
• Select Column quality.

Correct answer: Select Column distribution.

To identify missing data in the 'State/Region' column and understand its distribution, you should use Power Query Editor's data profiling tools. 'Column profile' provides a comprehensive view of column statistics, including error and empty counts, while 'Column distribution' shows the unique and distinct values. These features help quickly spot any missing or unexpected states/regions, aiding in data quality assessment.