MCQs on Data Transformation Basics | Power BI
Explore the fundamentals of data transformation in Power BI, including Power Query basics, cleaning and shaping data, creating calculated columns, and merging or appending queries. Perfect for beginners and professionals.
Introduction to Power Query
- What is the primary purpose of Power Query in Power BI?
a) Visualizing data
b) Transforming and preparing data
c) Storing data in the cloud
d) Writing SQL queries - Power Query operates on which principle?
a) Direct Query
b) Data Mashup
c) Data Modeling
d) Scripting - Which interface is used to perform data transformation tasks in Power Query?
a) Data Model Editor
b) Query Editor
c) Dashboard Editor
d) Report View - How does Power Query store its transformation steps?
a) As VBA Macros
b) As a script in M Language
c) In JSON files
d) Directly in the database - What is the default output of Power Query after transformation?
a) Data visualization
b) A new table in the data model
c) A DAX query
d) SQL query results
Cleaning and Shaping Data
- What is the first step in data cleaning within Power Query?
a) Applying filters
b) Loading the data
c) Removing unnecessary columns
d) Fixing data types - In Power Query, which option helps you remove duplicate rows?
a) Keep Top Rows
b) Remove Errors
c) Remove Duplicates
d) Replace Values - Which transformation can fix inconsistent capitalization in text data?
a) Split Column
b) Trim and Clean
c) Format Case
d) Merge Queries - What feature in Power Query can handle null values in a dataset?
a) Replace Values
b) Group Data
c) Fill Down
d) Pivot Columns - What is the benefit of shaping data in Power Query?
a) It improves the data model design
b) It makes data ready for analysis
c) It creates data visualizations
d) It saves data in external databases - What does the “Unpivot Columns” operation do in Power Query?
a) Combines multiple columns into one
b) Turns columns into rows
c) Splits a single column into two
d) Removes unwanted columns - In Power Query, what does the “Group By” transformation do?
a) Sorts data alphabetically
b) Aggregates data based on a field
c) Merges two datasets
d) Deletes unwanted rows - How does “Split Column by Delimiter” function help?
a) It removes columns with delimiters
b) It divides a single column into multiple columns
c) It combines multiple rows
d) It appends two datasets - What is the “Transpose” function used for?
a) Converting rows into columns and vice versa
b) Sorting data alphabetically
c) Removing duplicates
d) Appending multiple queries - Which of the following is NOT a data cleaning operation in Power Query?
a) Replace Errors
b) Pivot Columns
c) Create Relationships
d) Change Data Types
Adding Calculated Columns
- What is a calculated column in Power BI?
a) A column added in the Power Query Editor
b) A column created using DAX formulas
c) A column derived from SQL queries
d) A column generated during data export - Which formula language is used to create calculated columns in Power BI?
a) SQL
b) M Language
c) DAX
d) Python - Calculated columns are created at what level in Power BI?
a) Visualization level
b) Data model level
c) Query Editor level
d) Dashboard level - How do calculated columns differ from measures?
a) Columns aggregate data; measures store raw data
b) Measures are pre-aggregated; columns store row-level data
c) Measures use M language; columns use DAX
d) Columns are dynamic; measures are static - Which of these is an example of a DAX function used in calculated columns?
a) SUMX
b) VLOOKUP
c) FILTERXML
d) COUNTIFS - What is a key disadvantage of using calculated columns excessively?
a) They increase visual complexity
b) They require SQL expertise
c) They reduce performance
d) They cannot handle numeric data - In Power BI, where can you create calculated columns?
a) Power Query
b) Data View
c) Dashboard Editor
d) Report View
Merging and Appending Queries
- What is the main purpose of merging queries in Power BI?
a) Joining tables based on a common key
b) Creating new columns
c) Aggregating data
d) Adding new rows - What does appending queries accomplish in Power BI?
a) Adds new rows from one query to another
b) Removes duplicate rows
c) Joins tables horizontally
d) Deletes unwanted rows - Which join type is used in Power BI when merging queries to include all records from both tables?
a) Inner Join
b) Full Outer Join
c) Left Anti Join
d) Right Join - In Power BI, which feature is used to append queries?
a) Merge Queries
b) Append Queries
c) Unpivot Columns
d) Transform Columns - What must be true for appending queries to work correctly?
a) The queries must have a common key
b) The columns must match in number and data type
c) Both datasets must have the same number of rows
d) Both datasets must use the same delimiter - Which operation combines columns from two tables into a single table?
a) Append Queries
b) Merge Queries
c) Transpose Table
d) Group Data - What is the primary difference between merging and appending queries?
a) Merging adds rows; appending combines columns
b) Merging combines columns; appending adds rows
c) Merging removes duplicates; appending sorts data
d) Merging is faster than appending - How does Power BI handle unmatched rows when merging queries with a Left Join?
a) It excludes unmatched rows
b) It fills unmatched rows with null values
c) It duplicates unmatched rows
d) It creates a separate table
Answer Key
| Qno | Answer |
|---|---|
| 1 | b) Transforming and preparing data |
| 2 | b) Data Mashup |
| 3 | b) Query Editor |
| 4 | b) As a script in M Language |
| 5 | b) A new table in the data model |
| 6 | b) Loading the data |
| 7 | c) Remove Duplicates |
| 8 | c) Format Case |
| 9 | a) Replace Values |
| 10 | b) It makes data ready for analysis |
| 11 | b) Turns columns into rows |
| 12 | b) Aggregates data based on a field |
| 13 | b) It divides a single column into multiple columns |
| 14 | a) Converting rows into columns and vice versa |
| 15 | c) Create Relationships |
| 16 | b) A column created using DAX formulas |
| 17 | c) DAX |
| 18 | b) Data model level |
| 19 | b) Measures are pre-aggregated; columns store row-level data |
| 20 | a) SUMX |
| 21 | c) They reduce performance |
| 22 | b) Data View |
| 23 | a) Joining tables based on a common key |
| 24 | a) Adds new rows from one query to another |
| 25 | b) Full Outer Join |
| 26 | b) Append Queries |
| 27 | b) The columns must match in number and data type |
| 28 | b) Merge Queries |
| 29 | b) Merging combines columns; appending adds rows |
| 30 | b) It fills unmatched rows with null values |