Microsoft Power BI Power Query and Data Transformation 1 — Questions and Answers
Question 1: What language does Power Query use for its advanced editor transformations?
- DAX
- SQL
- M language (M formula language) (Correct answer)
- Python
Correct answer: M language (M formula language)
Power Query uses M (also called the M formula language or Power Query Formula Language) to define data transformation steps.
Question 2: What does the 'Remove Duplicates' step in Power Query do?
- Removes rows with null values
- Keeps only the first occurrence of each duplicate row based on selected columns (Correct answer)
- Removes duplicate column names
- Deletes rows that appear more than twice
Correct answer: Keeps only the first occurrence of each duplicate row based on selected columns
Remove Duplicates keeps the first occurrence of each unique combination of values in selected columns and removes subsequent duplicate rows.
Question 3: In Power Query, what does 'Unpivot Columns' do to a table?
- Transposes rows and columns
- Converts selected column headers and their values into attribute-value rows (Correct answer)
- Removes selected columns from the table
- Pivots data from rows into columns
Correct answer: Converts selected column headers and their values into attribute-value rows
Unpivot transforms wide-format data by turning multiple column headers into an Attribute column and their values into a Value column.
Question 4: What is a 'Query Dependency' in Power Query?
- A required parameter for every query
- A relationship between queries where one query references or is built upon another (Correct answer)
- An error that occurs when a query fails
- A scheduled refresh dependency
Correct answer: A relationship between queries where one query references or is built upon another
A Query Dependency exists when one Power Query query uses another as its source, creating a dependency chain visible in the Query Dependencies view.
Question 5: Which Power Query step type allows you to combine rows from two tables with the same columns?
- Merge Queries
- Append Queries (Correct answer)
- Join Queries
- Union Queries
Correct answer: Append Queries
Append Queries combines rows from two or more tables with matching column structures, stacking them vertically.
Question 6: What does 'Merge Queries' do in Power Query?
- Combines rows from two tables by stacking them
- Joins two tables based on matching key columns, similar to a SQL JOIN (Correct answer)
- Merges two .pbix files together
- Combines multiple columns into one
Correct answer: Joins two tables based on matching key columns, similar to a SQL JOIN
Merge Queries performs a join between two tables based on one or more matching key columns, with options for Left Outer, Inner, Full Outer, and other join types.
Microsoft Power BI Power Query and Data Transformation 1
What language does Power Query use for its advanced editor transformations?