QUESTION 1

Which of these operations adds rows to an existing table?

Ⓐ Group By.
Ⓑ Pivoting.
Ⓒ Merging queries.
Ⓓ Appending queries.

.

---

QUESTION 2

When would you use a tool from the Transform tab over the Add Column tab?

Ⓐ When you want to overwrite the values of existing columns.
Ⓑ When you want to keep the values of existing columns.
Ⓒ When you want to create new columns
Ⓓ Both B & C

.

---

QUESTION 3

What can be useful to create unique IDs and form relationships between tables?

Ⓐ Adding a column from examples.
Ⓑ Grouping data.
Ⓒ Adding an index column.
Ⓓ Appending queries.

.

---

QUESTION 4

Which Power Query tool can you use when you know the outcome of a column you want but don't know which transformation(s) to use?

Ⓐ Column from examples.
Ⓑ Conditional column.
Ⓒ Custom column.
Ⓓ Index column.

.

---

QUESTION 5

What happens "under the hood" whenever you apply a transformation in Power Query?

Ⓐ The same transformation is applied to the source data.
Ⓑ The changes are permanent and cannot be modified.
Ⓒ The Query Editor writes the corresponding DAX code for the applied step.
Ⓓ The Query Editor writes the corresponding M code for the applied step.

.

---

QUESTION 6

Which two blocks make up the M code that runs your query?

Ⓐ do & while.
Ⓑ if & then.
Ⓒ for & each.
Ⓓ let & in.

.

---

QUESTION 7

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

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?

Ⓐ Select 2019, 2020, and 2021
columns.

Ⓑ Select unpivot other columns.

Ⓒ Rename the Attribute column
as Year and the Value column 
as Sales.

Ⓓ Select the Month and 
MonthNumber columns.

Ⓔ Select Transpose.

.

---

QUESTION 8

Which Power Query tool can you use to roll-up daily transaction data into monthly transactions?

Ⓐ Merging Queries.
Ⓑ Appending Queries.
Ⓒ Group By.
Ⓓ Pivot Columns.

.

---

QUESTION 9

You work as an analyst at Cat Slacks and you've just been handed a csv file with yearly sales by department. After connecting to it in Power BI, you notice that each year has its own column. Which Power Query tool can you use to turn the multiple "Year" columns into row?

Ⓐ Pivot.
Ⓑ Unpivot.
Ⓒ Transpose.
Ⓓ Group By.

.

---

QUESTION 10

Which of these statements is NOT true about merging queries?

Ⓐ Merging queries allows you
to join tables based on a
common column.

Ⓑ Merging adds columns to an
existing table.

Ⓒ You should merge tables
whenever possible.

Ⓓ You can merge queries by
different join kinds (left
outer, inner, etc.).

.

---

QUESTION 11

You import two Microsoft Excel tables named Customer and Address into Power Query.

Customer contains the following columns:

• Customer ID

• Customer Name

• Email Address

Address contains the following columns:

• 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?

Ⓐ Transpose the Customer and
Address tables.

Ⓑ Append the Customer and
Address tables.

Ⓒ Merge the Customer and
Address tables.

Ⓓ  Group the Customer and
Address tables by the Address
ID column.

.

---

QUESTION 12

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 Suppliers.
You need to combine the Supplier 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?

Select the appropriate options in the answer area.

⑴ Options to use to combine
Supplier tables:
Ⓐ Append Querries
Ⓑ Append Queries As New
Ⓒ Merge Queries
Ⓓ Merge Queries As New


⑵ Action to perform on the
original two SQL database
queries:
Ⓐ Delete the queries
Ⓑ Disable including the query in report refresh
Ⓒ Disable loading the query to the data model
Ⓓ Duplicate the queries

.

---

QUESTION 13

In Power Query Editor, you have three queries:

• ProductCategory

• ProductSubCategory

• 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?

⑴ 
    Left Table |    Right Table     | Join Kind
    ---------------------------------------------
    Product    | ProductSubCategory |    Ⓟ


Ⓟ:
Ⓐ Full Outer
Ⓑ Inner
Ⓒ Left Anti 
Ⓓ Left Outer
Ⓔ Right Anti
Ⓕ Right Outer




⑵ 
         Left Table     |   Right Table   | Join Kind
     -------------------------------------------------
     ProductSubCategory | ProductCategory |    Ⓠ


Ⓠ:
Ⓐ Full Outer
Ⓑ Inner
Ⓒ Left Anti 
Ⓓ Left Outer
Ⓔ Right Anti
Ⓕ Right Outer

.

---

QUESTION 14

You are building a Power Bl report that uses data from an Azure SQL database named erpdb. You Import the following tables:

You need to perform the following analyses:
(1) Orders sold over time that include a measure of the total order value.
(2) Orders by attributes of products sold.

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

What should you do first?

Ⓐ From Power Query, merge the
Orders query and the Order
Line Items query.

Ⓑ Calculate the count of
orders per product by using a
DAX function.

Ⓒ Create a calculated column
that adds a list of product
categories to the Orders table
by using a DAX function. 

Ⓓ From Power Query, merge the
Order Line Items query and the
Products query.

.

---

QUESTION 15

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?

Ⓐ Apply a transformation to
extract the last 11 characters
of the Logged column and set
the data type of the new
column to Date.

Ⓑ Change the data type of the
Logged column to Date.

Ⓒ Split the Logged column by
using at as the delimiter.

Ⓓ Apply a transformation to
extract the first 11
characters of the Logged
column.

.

---

QUESTION 16

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?

Ⓐ From Power Query Editor,
remove errors from the table.

Ⓑ From Power Query Editor,
select Table1, and then select
remove duplicates.

Ⓒ From Power Query Editor,
merge Table1 and Table2. 

Ⓓ From Power BI Desktop,
import the data from Excel,
and then select Table1 and
Table2.

Ⓔ From Power Query Editor,
append Table2 to Table1.

.

---