1-15

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?

• Get data from a SharePoint folder and enter the site URL. Select Transform, then filter by the folder path to the manufacturing reports library.

• Import the Excel files directly from the document library into Power BI without any transformations.

• Use Power Query to connect to the SharePoint site and load all files from all libraries.

• Create a new Excel file that consolidates all manufacturing reports and then import that file into Power BI.

You plan to create the Power BI model shown in the exhibit. (Click the Exhibit tab.) The data has the following refresh requirements: Customer must be refreshed daily. Date must be refreshed once every three years. Sales must be refreshed in near real time. SalesAggregate must be refreshed once per week. You need to select the storage modes for the tables. The solution must meet the following requirements: Minimize the load times of visuals. Ensure that the data is loaded to the model based on the refresh requirements. Which storage mode should you select for each table?

• Customer: Import, Date: Import, Sale: DirectQuery, SaleAggregate: Import

• Customer: Dual, Date: Dual, Sale: DirectQuery, SaleAggregate: Import

• Customer: DirectQuery, Date: Import, Sale: Import, SaleAggregate: Dual

• Customer: Import, Date: Dual, Sale: Import, SaleAggregate: DirectQuery

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.

• SharePoint folder

• Web

• SQL Server

• Azure Blob Storage

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?

• Merge the Customer and Address tables.

• Create a new table for each customer.

• Filter the Address table by Customer ID.

• Remove duplicates from the Customer table.

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?

• From Power BI Desktop, Select Get Data, and then Select Folder. From Power Query Editor, expand the Attributes column. From Power Query Editor, combine the Content column.

• From Power BI Desktop, Select Get Data, and then Select File. From Power Query Editor, filter the Content column. From Power Query Editor, expand the Attributes column.

• From Power BI Desktop, Select Get Data, and then Select Folder. From Power Query Editor, filter the Attributes column. From Power Query Editor, combine the Content column.

• From Power BI Desktop, Select Get Data, and then Select Folder. From Power Query Editor, expand the Content column. From Power Query Editor, filter the Attributes column.

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?

• Set Data Connectivity mode to DirectQuery.

• Use Import mode for data connectivity.

• Set up a scheduled refresh every hour.

• Connect using a static data snapshot.

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.

• 69 different values

• 50 different values

• 4 non-null values

• 10 non-null values

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 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.

• Merge and Disable the query load

• Append and Enable the query load

• Join and Keep the query load

• Merge and Enable the query load

In Power Query Editor, you have three queries named ProductCategory, ProductSubCategory, and Product. Every Product has a ProductSubCategory. Not every ProductSubCategory has a parent ProductCategory. You need to merge the three queries into a single query. The solution must ensure the best performance in Power Query. How should you merge the tables?

• Inner join for ProductCategory and ProductSubCategory, Left outer join for ProductSubCategory and Product

• Left outer join for ProductCategory and ProductSubCategory, Inner join for ProductSubCategory and Product

• Full outer join for all three queries

• Inner join for all three queries

You have two Azure SQL databases that contain the same tables and columns. For each database, you create a query that retrieves data from a table named Customer. You need to combine the Customer tables into a single table. The solution must minimize the size of the data model and support scheduled refresh in powerbi.com. What should you do?

• Append Queries as New

• Merge Queries

• Create a new table with UNION

• Load both queries to the data model