Challenge your understanding of advanced data transformation techniques and concepts in Power Query, including merging queries, using functions, pivoting data, and managing errors. This quiz is ideal for users looking to optimize data preparation workflows and enhance query efficiency.
Which operation in Power Query allows you to combine two tables based on common columns, similar to performing a database join?
Explanation: Merge Queries lets you join two tables together based on shared columns, much like a typical database join. Promote Headers changes row data into column headers, which does not combine tables. Append Queries stacks tables vertically, rather than combining them based on keys. Transpose Table switches rows and columns but isn't used for joining data from multiple sources.
If you want to create a new column in Power Query that returns 'Yes' when the value in the 'Score' column is greater than 80 and 'No' otherwise, which feature should you use?
Explanation: The Conditional Column feature allows you to add a new column based on if-then-else logic using the values in existing columns. Group By is for summarizing data, Custom Sort arranges data order, and Remove Duplicates eliminates repeated rows but doesn’t create new columns based on logic.
Which method lets you repeatedly apply the same transformation logic to multiple columns using a single reusable block in Power Query?
Explanation: Custom Functions allow you to define a transformation once and apply it to different columns or tables, ensuring consistency and saving time. Auto Filter filters data but does not contain logic blocks. Static Query is not a term in this context. Manual Entry does not enable reusability or automation of logic.
Which tool in Power Query helps identify and inspect rows where steps such as data conversions have failed due to non-matching data types?
Explanation: Keep Errors creates a view displaying only the rows that caused errors in specific steps, making troubleshooting more efficient. Group By summarizes your data but does not focus on errors. Cross Join is not a native feature in all Power Query environments. Fill Down populates null values in a column, not error handling.
If you want to convert columns into attribute–value pairs so that each original column becomes a separate row, which operation should you use in Power Query?
Explanation: Unpivot Columns transforms columns into rows, ideal for attribute–value pair structures. Pivot Columns does the opposite, converting rows into columns. Split Column divides a single column into multiple columns, not rows. Rename Column changes a column’s name without altering data layout.
To ensure missing values are filled with the preceding non-null value in a column using Power Query, which feature should you apply?
Explanation: Fill Down carries the last non-null value downward to fill gaps, which is helpful with hierarchical or sparse data. Top Row doesn't modify missing values, Drill Down focuses on a specific cell, and Split Rows creates multiple rows from the content of a single row, but not for filling nulls.
Which step should be used in Power Query to keep only the first 10 rows of a table, for example, when preparing a data sample?
Explanation: Keep Top Rows retains a specified number of top rows in your query output, suitable for sampling or previewing data. Remove Columns removes fields but not rows. Sort Descending alters row order, not the number of rows. Replace Values updates cell values but does not affect the number of rows.
Which feature lets you quickly assess column quality by displaying metrics such as distinct values, errors, and empty values in Power Query?
Explanation: The Column Profile view provides metrics about each column, like data types, value distribution, errors, and empties, aiding in data quality assessment. Advanced Filter refines queries but doesn't summarize data quality. Merge Columns combines columns, and Replace Errors changes erroneous values but doesn't analyze column quality.
What transformation should you apply to get a new column showing only the year part from a full date column in Power Query?
Explanation: Date.Year extracts only the year portion from a date, resulting in a new column with year values. Extract Letters works on text data, not dates. Concat Columns combines columns, and Split by Delimiter divides text based on a specified delimiter but isn't designed for isolating year from dates.
If you want to create a copy of a query to modify it separately while keeping the original unchanged, which option should you use in Power Query?
Explanation: Duplicate makes a new copy of the entire query, allowing independent changes without affecting the original. Reference creates a new query that depends on the original, so changes upstream may affect it. Auto Merge and Filter Out are unrelated to duplicating or copying queries for separate editing.