Microsoft Certified Data Analyst Associate Power Query and Data Transformation 1 — Questions and Answers
Question 1: What language is used for transformations in Power Query?
- DAX
- SQL
- M (Power Query Formula Language) (Correct answer)
- Python
Correct answer: M (Power Query Formula Language)
Power Query uses the M language (Power Query Formula Language) to define all data transformation steps.
Question 2: What does the 'Merge Queries' operation in Power Query do?
- Appends rows from one table to another
- Joins two tables based on matching columns, similar to a SQL JOIN (Correct answer)
- Combines columns from the same table
- Merges duplicate rows into one
Correct answer: Joins two tables based on matching columns, similar to a SQL JOIN
Merge Queries combines two tables based on matching key columns using join types such as Left Outer, Inner, or Full Outer.
Question 3: What does the 'Append Queries' operation in Power Query do?
- Joins two tables on a common key
- Stacks rows from multiple tables into a single table (Correct answer)
- Adds new calculated columns to a table
- Appends a SQL query to an M script
Correct answer: Stacks rows from multiple tables into a single table
Append Queries combines rows from two or more tables with matching column structures into one table, similar to SQL UNION.
Question 4: What is query folding in Power Query?
- Collapsing multiple queries into one in the Query Editor
- The process of translating M transformations into native source queries (e.g., SQL) for execution at the source (Correct answer)
- Folding long M code into a single line
- Caching query results for faster load
Correct answer: The process of translating M transformations into native source queries (e.g., SQL) for execution at the source
Query folding occurs when Power Query translates transformation steps into native SQL (or other source queries) so the source executes them instead of the engine, improving performance.
Question 5: What does the 'Unpivot Columns' transformation do in Power Query?
- Rotates row data into column headers
- Converts multiple attribute columns into two columns: Attribute and Value (Correct answer)
- Removes duplicate column headers
- Transposes the entire table
Correct answer: Converts multiple attribute columns into two columns: Attribute and Value
Unpivoting converts wide-format data (many columns) into a long format with an Attribute column and a Value column, making it easier to analyze.
Question 6: How can you reference a parameter in a Power Query M expression?
- Using @ symbol prefix
- By typing the parameter name directly as a value in the expression (Correct answer)
- Using the #{} syntax
- By importing it with a SQL variable
Correct answer: By typing the parameter name directly as a value in the expression
Power Query parameters are referenced by their name directly in M expressions, and they appear as named values in the query context.
Microsoft Certified Data Analyst Associate Power Query and Data Transformation 1
What language is used for transformations in Power Query?