259 Teil 2: Power Query

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

1. HOTSPOT 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? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area

1. DRAG DROP 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? To answer, drag the appropriate merge types to the correct queries. Each merge type 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. Select and Place:

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?

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

DRAG DROP You have a Microsoft Excel workbook that contains two sheets named Sheet1 and Sheet2. Sheet1 contains the following table named Table1

Sheet2 contains the following table named Table2

You need to use Power Query Editor to combine the products from Table1 and Table2 into the following table that has one column containing no duplicate values

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

12.

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

13.

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:

1. DRAG DROP 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

1. DRAG DROP You receive annual sales data that must be included in Power BI reports. From Power Query Editor, you connect to the Microsoft Excel source shown in the following exhibit.

You need to create a report that meets the following requirements:

• Visualizes the Sales value over a period of years and months

• Adds a slicer for the month

• Adds a slicer for the year

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

HOTSPOT You have the Azure SQL databases shown in the following table

You plan to build a single PBIX file to meet the following requirements:

• Data must be consumed from the database that corresponds to each stage of the development lifecycle.

• Power BI deployment pipelines must NOT be used.

• The solution must minimize administrative effort.

What should you do? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point

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

DRAG DROP CertyIQ You use Power Query Editor to preview the data shown in the following exhibit

You need to clean and transform the query so that all the rows of data are maintained, and error values in the discount column are replaced with a discount of 0.05. The solution must minimize administrative effort. 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.

1. HOTSPOT CertyIQ You attempt to use Power Query Editor to create a custom column and receive the error message 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

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

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

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

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

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

31. HOTSPOT You have a folder that contains 50 JSON files. CertyIQ You need to use Power BI Desktop to make the metadata of the files available as a single dataset. The solution must NOT store the data of the JSON files. Which type of data source should you use, and which transformation should you perform? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point

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

1. You are creating a report in Power BI Desktop. You load a data extract that includes a free text field named coll. You need to analyze the frequency distribution of the string lengths in col1. The solution must not affect the size of the model. What should you do?

1. You have a Power BI query named Sales that imports the columns shown in the following table.

Users only use the date part of the Sales_Date field. Only rows with a Status of Finished are used in analysis. You need to reduce the load times of the query without affecting the analysis. Which two actions achieve this goal? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

A. Remove the rows in which Sales[Status] has a value of Canceled. B. Remove Sales[Sales_Date]. C. Change the data type of Sale[Delivery_Time] to Integer. D. Split Sales[Sale_Date] into separate date and time columns. E. Remove Sales[Canceled Date]

44.

DRAG DROP You create a data model in Power BI. Report developers and users provide feedback that the data model is too complex. The model contains the following tables

The model has the following relationships:

✑ There is a one-to-one relationship between Sales_Region and Region_Manager.

✑ There are more records in Manager than in Region_Manager, but every record in Region_Manager has a corresponding record in Manager.

✑ There are more records in Sales_Manager than in Sales_Region, but every record in Sales_Region has a corresponding record in Sales_Manager.

You need to denormalize the model into a single table. Only managers who are associated to a sales region must be included in the reports. 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. NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select. Select and Place:

1. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records. During the development process, you need to import a sample of the data from the Order table. Solution: From Power Query Editor, you import the table and then add a filter step to the query. Does this meet the goal?

1. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records. During the development process, you need to import a sample of the data from the Order table. Solution: You write a DAX expression that uses the FILTER function. Does this meet the goal?

1. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records. During the development process, you need to import a sample of the data from the Order table. Solution: You add a WHERE clause to the SQL statement. Does this meet the goal?

1. DRAG DROP You are preparing a financial report in Power BI. You connect to the data stored in a Microsoft Excel spreadsheet by using Power Query Editor as shown in the following exhibit.

You need to prepare the data to support the following:

✑ Visualizations that include all measures in the data over time

✑ Year-over-year calculations for all the measures 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. Select and Place

1. You have the tables shown in the following table.

The Impressions table contains approximately 30 million records per month. You need to create an ad analytics system to meet the following requirements: ✑ Present ad impression counts for the day, campaign, and site_name. The analytics for the last year are required. Minimize the data model size. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

1. DRAG DROP You receive revenue data that must be included in Microsoft Power BI reports. You preview the data from a Microsoft Excel source in Power Query as shown in the following exhibit

You plan to create several visuals from the data, including a visual that shows revenue split by year and product. You need to transform the data to ensure that you can build the visuals. The solution must ensure that the columns are named appropriately for the data that they contain. 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:

1. You are building a data model for a Power BI report. You have data formatted as shown in the following table.

You need to create a clustered bar chart as shown in the following exhibit

What should you do?

A. From Power Query Editor, split the Machine-User column by using a delimiter. B. From Power Query Editor, create a column that contains the last three digits of the Machine-User column. C. In a DAX function, create two calculated columns named Machine and User by using the SUBSTITUTE function. D. In a DAX function, create two measures named Machine and User by using the SUBSTITUTE function