Microsoft Certified Data Analyst Associate Power Query and Data Transformation

Question 1

What language is used for transformations in Power Query?

Accepted Answer

M (Power Query Formula Language)

Answer

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?

Accepted Answer

Joins two tables based on matching columns, similar to a SQL JOIN

Answer

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?

Accepted Answer

Stacks rows from multiple tables into a single table

Answer

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?

Accepted Answer

The process of translating M transformations into native source queries (e.g., SQL) for execution at the source

Answer

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?

Accepted Answer

Converts multiple attribute columns into two columns: Attribute and Value

Answer

Unpivoting converts wide-format data (many columns) into a long format with an Attribute column and a Value column, making it easier to analyze.

Microsoft Certified Data Analyst Associate Practice Test

Microsoft Certified Data Analyst Associate Practice Test

Microsoft Certified Data Analyst Associate Power Query and Data Transformation