Free Analyzing Data with Microsoft Power BI Questions and Answers

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?

Accepted Answer

Column quality

Answer

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

Accepted Answer

Select Column distribution.

Answer

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?

Accepted Answer

Remove the Subject and Body columns during the import.

Answer

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?

Accepted Answer

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

Answer

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?

Accepted Answer

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

Answer

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.

Microsoft Certified Data Analyst Associate Practice Test

Microsoft Certified Data Analyst Associate Practice Test

Free Analyzing Data with Microsoft Power BI Questions and Answers