Microsoft Certified Data Analyst Associate Power Query and Data Transformation 2 — Questions and Answers
Question 1: What is a Power Query function?
- A DAX function called from Power Query
- A reusable M query that accepts parameters and returns a value or table (Correct answer)
- A built-in connector for external APIs
- A SQL stored procedure called during refresh
Correct answer: A reusable M query that accepts parameters and returns a value or table
Power Query functions are reusable M queries parameterized to accept inputs, enabling you to apply the same transformation logic to multiple data sources.
Question 2: What does the 'Group By' transformation do in Power Query?
- Sorts rows by a column value
- Aggregates rows with the same key column values using operations like Sum, Count, or Average (Correct answer)
- Groups multiple queries into a folder
- Applies conditional formatting by group
Correct answer: Aggregates rows with the same key column values using operations like Sum, Count, or Average
Group By aggregates data by specified key columns, computing summary statistics like sum or count for each group.
Question 3: Which data type in Power Query should be used for columns that will be used in DAX date calculations?
- Text
- Whole Number
- Date or Date/Time (Correct answer)
- Duration
Correct answer: Date or Date/Time
Columns used in DAX time intelligence functions must be of the Date or Date/Time data type for proper calendar-based calculations.
Question 4: What is the purpose of the 'Applied Steps' pane in Power Query Editor?
- It shows the query execution plan
- It lists all transformation steps applied to the query in order, allowing editing or deletion (Correct answer)
- It displays the SQL generated by query folding
- It shows data quality errors per column
Correct answer: It lists all transformation steps applied to the query in order, allowing editing or deletion
The Applied Steps pane records every transformation step as a named M expression, letting you review, reorder, or delete individual steps.
Question 5: What does enabling 'Column Quality' in Power Query Editor display?
- Data type icons for each column
- Percentage of valid, error, and empty values for each column (Correct answer)
- A histogram of value distribution
- The number of unique values per column
Correct answer: Percentage of valid, error, and empty values for each column
Column Quality shows the percentage of valid, error, and empty values in each column, helping you quickly identify data quality issues.
Question 6: What is the 'Table.TransformColumnTypes' function used for in M language?
- Renaming columns in a table
- Changing the data types of one or more columns in a table (Correct answer)
- Filtering rows based on column values
- Splitting a column into multiple columns
Correct answer: Changing the data types of one or more columns in a table
Table.TransformColumnTypes changes the data type of specified columns, which is automatically generated when you set types in the Power Query UI.
Microsoft Certified Data Analyst Associate Power Query and Data Transformation 2
What is a Power Query function?