PL-300: Microsoft Power BI Data Analyst
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
Answer is When you want to overwrite the values of existing columns
The Transform tab includes tools to modify existing columns by overwritting its values
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
Answer is Adding an index column
Index columns contain a list of sequential values that can be used to identify each unique row in a table, which is why they are often used to create unique IDs and form relationships between tables.
Based on the conditional column above, what Price Range value would be given to a Unit_Price of 500?
Low
Mid
High
Unclassified
Answer is Mid
Conditional columns allow you to define new fields based on logical rules (IF statements). In this example, the value of 500 is greater that 100, but less than 1000, which is why the output is "Mid".
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
Answer is Column from examples
Columns from examples allow you to add new columns by providing an example value
Which Power Query tool can you use to roll-up daily transaction data into monthly transactions
Merging Queries
Appending Queries
Group By
Pivot Columns
Answer is Group By
Group by allows you to aggregate your data at a different level (like daily data into monthly).
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 rows?
Pivot
Unpivot
Transpose
Group By
Answer is Unpivot
“Pivoting” is a fancy way to describe the process of turning distinct row values into columns (“pivoting”) or turning columns into rows (“unpivoting”).
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.)
Answer is You should merge tables whenever possible
Just because you can merge tables, doesn't mean you should. It's better to keep tables separate and define relationships between them in your data model.
Which of these operations adds rows to an existing table?
Group By
Pivoting
Merging queries
Appending queries
Answer is Appending queries
Appending queries allows you to combine (or stack) tables that share the exact same column structure and data types, which adds rows to the existing table
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
Answer is The Query Editor writes the corresponding M code for the applied step
Data Mashup, or M code, is the formula language that drives Power Query. Whenever you use UI tools to apply a transformation, the corresponding M code is automatically generated.
Which two blocks make up the M code that runs your query?
do & while
if & then
for & each
let & in
Answer is let & in
Opening the advanced editor allows you to see the M code that makes up your query, which consists of two blocks: let (the definition of all variables) & in (the output of your query)