MCQs on Advanced Data Transformation | Power BI

Unlock Power BI’s Advanced Data Transformation with These MCQs
Dive deeper into Power BI with these 30 multiple choice questions focused on advanced Power Query techniques, parameterizing queries, and using custom M code scripts for more efficient data transformation.

---

Advanced Power Query Techniques

1. What is the primary function of Power Query in Power BI?
A) Visualizing data
B) Transforming and cleaning data
C) Running DAX calculations
D) Storing historical data

2. What does the “Group By” transformation in Power Query do?
A) Aggregates data based on a column
B) Sorts data alphabetically
C) Merges multiple tables
D) Applies custom formatting

3. Which of the following transformations can be applied using Power Query?
A) Splitting columns
B) Merging tables
C) Filtering rows
D) All of the above

4. In Power Query, which function is used to remove duplicate rows from a table?
A) RemoveRows()
B) RemoveDuplicates()
C) RemoveBlankRows()
D) RemoveDups()

5. What is the main purpose of the “Unpivot” transformation in Power Query?
A) To combine multiple columns into one
B) To split one column into multiple
C) To transpose rows and columns
D) To filter data based on values

---

Parameterizing Queries

6. What is the role of parameters in Power BI queries?
A) To generate new tables
B) To dynamically modify the query behavior
C) To store data temporarily
D) To define report themes

7. Which type of parameter can be used to change query results dynamically in Power Query?
A) Query Parameter
B) Table Parameter
C) Column Parameter
D) Data Type Parameter

8. How do you create a parameter in Power Query?
A) Right-click on a column and select “Create Parameter”
B) Use the “Manage Parameters” option under the Home tab
C) Use DAX to define parameters
D) Parameters cannot be created in Power Query

9. Which of the following is NOT a valid parameter type in Power Query?
A) Text
B) Date/Time
C) Numeric Range
D) File Path

10. How do you use a parameter within a query in Power Query?
A) Directly replace values in the formula bar
B) Bind the parameter to a specific column
C) Use it to filter rows dynamically
D) Parameters cannot be used within queries

---

Using Custom M Code Scripts

11. What is M code in Power Query?
A) A programming language used for data transformation
B) A way to create reports
C) A method for visualizing data
D) A SQL-like query syntax

12. In Power Query, how can you view and edit the underlying M code?
A) Use the Advanced Editor
B) Right-click on a query and select “View Code”
C) M code is automatically hidden from the user
D) M code is edited through Power BI Desktop’s formula bar

13. What does the “let” statement do in an M code script?
A) Creates a new query
B) Defines variables and calculations
C) Executes a transformation
D) Stores the final result

14. Which M function is used to filter rows based on a condition?
A) Table.FilterRows()
B) Table.SelectRows()
C) Table.RemoveRows()
D) Table.AddColumns()

15. What is the result of the following M code snippet:
Table.AddColumn(Source, "NewColumn", each [Column1] * 2)
A) Adds a new column with values doubled from Column1
B) Removes all rows where Column1 is null
C) Creates a duplicate of Column1
D) Adds a new column with constant values

---

Advanced Data Transformation Concepts

16. Which transformation in Power Query is used to convert text to date format?
A) Date.FromText()
B) Text.ToDate()
C) ConvertTextToDate()
D) Date.Transform()

17. How do you apply conditional transformations in Power Query?
A) Using the “If-Else” statement in M code
B) Using the Conditional Column feature
C) By creating custom functions
D) Both A and B

18. How does Power Query handle null values during transformations?
A) Power Query ignores null values automatically
B) Null values are replaced by zeros
C) Null values must be explicitly handled with functions like if null then
D) Power Query always removes null values

19. What is the use of the “Expand” function in Power Query?
A) To break down a column into multiple rows
B) To expand a column containing records into separate fields
C) To duplicate columns for merging
D) To summarize the data into one row

20. How can you apply transformations to multiple columns in Power Query at once?
A) Use the “Transform All Columns” option
B) Create a custom function
C) Use the “Advanced Editor”
D) Apply transformations individually to each column

---

Optimizing Queries and Performance

21. What is the purpose of the “Buffer” function in M code?
A) To store query results temporarily for faster processing
B) To optimize the data refresh rate
C) To filter data based on user preferences
D) To create custom parameters

22. How can you improve the performance of large data transformations in Power Query?
A) Use more complex M code
B) Apply transformations at the source before importing data
C) Avoid filtering data
D) Use only basic transformations

23. What is the best practice when loading large datasets in Power Query?
A) Load all data at once
B) Filter the data to a smaller subset before loading
C) Use the “Load to Data Model” option for better performance
D) Use Power BI Service exclusively for large datasets

24. In Power Query, how can you combine multiple queries into a single table?
A) Use the “Merge Queries” option
B) Use the “Append Queries” option
C) Use the “Join Queries” option
D) Create a custom M code function

25. Which method helps in optimizing the load time of queries with high computational cost?
A) Avoiding the use of parameters
B) Avoiding complex M code functions
C) Applying filters early in the transformation process
D) Using a slower data connection

---

Advanced Use Cases and Functions

26. Which M function is used to join two tables based on a common column?
A) Table.AddColumn()
B) Table.Join()
C) Table.Combine()
D) Table.SelectColumns()

27. Which Power Query operation allows creating new columns based on an existing column?
A) Add Custom Column
B) Merge Columns
C) Add Column by Formula
D) Add Column by Transformation

28. What is the use of the “Record.FieldValues” function in M code?
A) To retrieve specific fields from a record
B) To remove a field from a record
C) To group fields into a table
D) To sort fields by value

29. Which M function is used to replace values in a column?
A) Table.ReplaceValue()
B) Column.Replace()
C) Value.Replacement()
D) Table.TransformColumn()

30. In Power Query, how do you create a dynamic column based on row conditions?
A) Using the “Add Conditional Column” option
B) Using DAX expressions in Power Query
C) Using custom M code with if statements
D) Creating a pivot table

---

Answers

QNo	Answer (Option with the text)
1	B) Transforming and cleaning data
2	A) Aggregates data based on a column
3	D) All of the above
4	B) RemoveDuplicates()
5	A) To combine multiple columns into one
6	B) To dynamically modify the query behavior
7	A) Query Parameter
8	B) Use the “Manage Parameters” option under the Home tab
9	D) File Path
10	C) Use it to filter rows dynamically
11	A) A programming language used for data transformation
12	A) Use the Advanced Editor
13	B) Defines variables and calculations
14	B) Table.SelectRows()
15	A) Adds a new column with values doubled from Column1
16	A) Date.FromText()
17	D) Both A and B
18	C) Null values must be explicitly handled with functions like if null then
19	B) To expand a column containing records into separate fields
20	B) Create a custom function
21	A) To store query results temporarily for faster processing
22	B) Apply transformations at the source before importing data
23	B) Filter the data to a smaller subset before loading
24	B) Use the “Append Queries” option
25	C) Applying filters early in the transformation process
26	B) Table.Join()
27	A) Add Custom Column
28	A) To retrieve specific fields from a record
29	A) Table.ReplaceValue()
30	C) Using custom M code with if statements