Microsoft Power BI Power Query and Data Transformation 2 — Questions and Answers
Question 1: What does 'Query Folding' mean in Power Query?
- Collapsing multiple query steps into one step manually
- The ability for Power Query to push transformation steps back to the data source as native queries (Correct answer)
- Reducing the number of columns in a query
- Combining multiple queries into a single result
Correct answer: The ability for Power Query to push transformation steps back to the data source as native queries
Query Folding means Power Query translates transformation steps into native source queries (like SQL), so the source executes them instead of Power Query processing everything in memory.
Question 2: In Power Query M language, what does the `#date(year, month, day)` function return?
- A text string representing a date
- A date value with the specified year, month, and day (Correct answer)
- The number of days since January 1, 1900
- A datetime value at midnight
Correct answer: A date value with the specified year, month, and day
#date() creates a date value from the specified year, month, and day integers in Power Query's M language.
Question 3: What is a 'Parameter' in Power Query and what is it used for?
- A table with parameter-value pairs imported from Excel
- A named value that can be referenced in queries to make them dynamic or configurable (Correct answer)
- A column data type setting
- A filter applied to query results
Correct answer: A named value that can be referenced in queries to make them dynamic or configurable
A Power Query Parameter is a named, typed value that can be referenced throughout your queries, enabling dynamic filtering, file paths, or connection strings.
Question 4: What does the 'Replace Values' transformation in Power Query do?
- Replaces null values with zeros automatically
- Replaces specified values in a column with a different value (Correct answer)
- Replaces the entire column with a calculated value
- Replaces the data type of a column
Correct answer: Replaces specified values in a column with a different value
Replace Values finds occurrences of a specified value in a column and replaces them with another value you define.
Question 5: What is the purpose of the 'Buffer' step (Table.Buffer) in Power Query?
- Saves the query result to a temporary file
- Forces evaluation and caches the table in memory to prevent re-evaluation in subsequent steps (Correct answer)
- Adds a data buffer for large dataset loading
- Enables streaming mode for large tables
Correct answer: Forces evaluation and caches the table in memory to prevent re-evaluation in subsequent steps
Table.Buffer caches the table contents in memory so that subsequent references to it don't re-evaluate the full transformation pipeline, improving performance in certain scenarios.
Question 6: In Power Query, what does the 'Split Column' transformation do?
- Divides a numeric column by a specified divisor
- Splits a text column into multiple columns based on a delimiter or character count (Correct answer)
- Separates a query into two independent queries
- Splits a date column into year, month, and day columns
Correct answer: Splits a text column into multiple columns based on a delimiter or character count
Split Column divides a text column into multiple new columns based on a delimiter character or fixed character width.
Microsoft Power BI Power Query and Data Transformation 2
What does 'Query Folding' mean in Power Query?