Question 6 of 258

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:

    Correct Answer:

    Box 1: Inner -

    Every Product has a ProductSubCategory.

    A standard join is needed.

    One of the join kinds available in the Merge dialog box in Power Query is an inner join, which brings in only matching rows from both the left and right tables.

    Box 2: Left outer -

    Not every ProductsubCategory has a parent ProductCategory.

    One of the join kinds available in the Merge dialog box in Power Query is a left outer join, which keeps all the rows from the left table and brings in any matching rows from the right table.

    Reference:

    https://docs.microsoft.com/en-us/power-query/merge-queries-inner https://docs.microsoft.com/en-us/power-query/merge-queries-left-outer

Question 7 of 258

You are building a Power BI report that uses data from an Azure SQL database named erp1.

You import the following tables.

You need to perform the following analyses:

✑ Orders sold over time that include a measure of the total order value

Orders by attributes of products sold

The solution must minimize update times when interacting with visuals in the report.

What should you do first?

    Correct Answer: D

    To efficiently analyze orders over time and by product attributes while minimizing update times, the data needs to be properly combined to optimize performance and relationships. The most effective approach is to merge the Orders table with the Order Line Items table. By doing this, you create a single comprehensive fact table that includes detailed order information, such as order values and product IDs. This allows for more efficient calculations and visualization interactions. Therefore, merging the Orders query and the Order Line Items query in Power Query is the appropriate first step.

Question 8 of 258

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?

    Correct Answer: A

    To load only the manufacturing reports to a table for analysis, you should get data from a SharePoint folder and enter the site URL. Then, select Transform and filter by the folder path to the manufacturing reports library. This approach allows you to connect directly to the SharePoint folder where the Excel files are stored and use the Power Query Editor to filter out only the relevant files, ensuring that only the necessary data is loaded for analysis.

Question 9 of 258

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:

    Correct Answer:

    Reference:

    https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data

Question 10 of 258

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?

    Correct Answer: D

    To analyze complaints by the date, you need to extract the date portion from the Logged column. The date format in the Logged column is 'YYYY-MM-DD', which is present at the beginning of the string. By extracting the first 11 characters, you isolate the date portion, allowing it to be transformed into a Date type for analysis while disregarding the unnecessary time information.