You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the date and time each complaint occurred. The data in Logged is in the following format: 2018-12-31 at 08:59.

You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy.

What should you do?

You have a Microsoft Excel file in a Microsoft OneDrive folder.

The file must be imported to a Power BI dataset.

You need to ensure that the dataset can be refreshed in powerbi.com.

Which two connectors can you use to connect to the file? Each correct answer presents a complete solution.

NOTE: Each correct selection is worth one point.

HOTSPOT -

You are profiling data by using Power Query Editor.

You have a table named Reports that contains a column named State. The distribution and quality data metrics for the data in State is shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

NOTE: Each correct selection is worth one point.

Hot Area:

HOTSPOT -

You have two CSV files named Products and Categories.

The Products file contains the following columns:

✑ ProductID

✑ ProductName

✑ SupplierID

✑ CategoryID

The Categories file contains the following columns:

✑ CategoryID

✑ CategoryName

✑ CategoryDescription

From Power BI Desktop, you import the files into Power Query Editor.

You need to create a Power BI dataset that will contain a single table named Product. The Product will table includes the following columns:

✑ ProductID

✑ ProductName

✑ SupplierID

✑ CategoryID

✑ CategoryName

✑ CategoryDescription

How should you combine the queries, and what should you do on the Categories query? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Hot Area:

You have an Azure SQL database that contains sales transactions. The database is updated frequently.

You need to generate reports from the data to detect fraudulent transactions. The data must be visible within five minutes of an update.

How should you configure the data connection?

You have a folder that contains 100 CSV files.

You need to make the file metadata available as a single dataset by using Power BI. The solution must NOT store the data of the CSV files.

Which three actions should you perform in sequence. To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Select and Place:

A business intelligence (BI) developer creates a dataflow in Power BI that uses DirectQuery to access tables from an on-premises Microsoft SQL server. The

Enhanced Dataflows Compute Engine is turned on for the dataflow.

You need to use the dataflow in a report. The solution must meet the following requirements:

✑ Minimize online processing operations.

✑ Minimize calculation times and render times for visuals.

✑ Include data from the current year, up to and including the previous day.

What should you do?

You publish a dataset that contains data from an on-premises Microsoft SQL Server database.

The dataset must be refreshed daily.

You need to ensure that the Power BI service can connect to the database and refresh the dataset.

Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

You attempt to connect Power BI Desktop to a Cassandra database.

From the Get Data connector list, you discover that there is no specific connector for the Cassandra database.

You need to select an alternate data connector that will connect to the database.

Which type of connector should you choose?

You have a project management app that is fully hosted in Microsoft Teams. The app was developed by using Microsoft Power Apps.

You need to create a Power BI report that connects to the project management app.

Which connector should you select?

You are creating a query to be used as a Country dimension in a star schema.

A snapshot of the source data is shown in the following table.
You need to create the dimension. The dimension must contain a list of unique countries.

Which two actions should you perform? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

From Power Query Editor, you attempt to execute a query and receive the following error message.

Datasource.Error: Could not find file.

What are two possible causes of the error? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

You have data in a Microsoft Excel worksheet as shown in the following table.
You need to use Power Query to clean and transform the dataset. The solution must meet the following requirements:

• If the discount column returns an error, a discount of 0.05 must be used.

• All the rows of data must be maintained.

• Administrative effort must be minimized.

What should you do in Power Query Editor?

You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the date and time each complaint occurred. The data in Logged is in the following format: 2018-12-31 at 08:59.

What should you do?

You have two Microsoft Excel workbooks in a Microsoft OneDrive folder.

Each workbook contains a table named Sales. The tables have the same data structure in both workbooks.

You plan to use Power BI to combine both Sales tables into a single table and create visuals based on the data in the table. The solution must ensure that you can publish a separate report and dataset.

Which storage mode should you use for the report file and the dataset file? To answer, drag the appropriate modes to the correct files. Each mode may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

NOTE: Each correct selection is worth one point.

You use Power Query to import two tables named Order Header and Order Details from an Azure SQL database. The Order Header table relates to the Order Details table by using a column named Order ID in each table.

You need to combine the tables into a single query that contains the unique columns of each table.

What should you select in Power Query Editor?

For the sales department at your company, you publish a Power BI report that imports data from a Microsoft Excel file located in a Microsoft SharePoint folder.

The data model contains several measures.

You need to create a Power BI report from the existing data. The solution must minimize development effort.

Which type of data source should you use?

You have a PBIX file that imports data from a Microsoft Excel data source stored in a file share on a local network.

You are notified that the Excel data source was moved to a new location.

You need to update the PBIX file to use the new location.

What are three ways to achieve the goal? Each correct answer presents a complete solution.

NOTE: Each correct selection is worth one point.

You import two Microsoft Excel tables named Customer and Address into Power Query. Customer contains the following columns:

✑ Customer ID

✑ Customer Name

✑ Phone

✑ Email Address

✑ Address ID

Address contains the following columns:

✑ Address ID

✑ Address Line 1

✑ Address Line 2

✑ City

✑ State/Region

✑ Country

✑ Postal Code

Each Customer ID represents a unique customer in the Customer table. Each Address ID represents a unique address in the Address table.

You need to create a query that has one row per customer. Each row must contain City, State/Region, and Country for each customer.

What should you do?

You have a Microsoft SharePoint Online site that contains several document libraries.

One of the document libraries contains manufacturing reports saved as Microsoft Excel files. All the manufacturing reports have the same data structure.

You need to use Power BI Desktop to load only the manufacturing reports to a table for analysis.

What should you do?

You need to update the Power BI model to ensure that the analysts can quickly build drill-downs from business unit to product in a visual.

What should you create?

You need to create the Top Customers report.

Which type of filter should you use, and at which level should you apply the filter? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

You need to create the On-Time Shipping report. The report must include a visualization that shows the percentage of late orders.

Which type of visualization should you create?

You need to ensure that the data is updated to meet the report requirements. The solution must minimize configuration effort.

What should you do?