Practice Test
True or False: The “Collapse Navigation” option in Power Query can help improve the performance.
- Answer: True
Explanation: The “Collapse Navigation” option can help bundle together all the steps of the navigation process into one single step which helps to reduce the loaded data, improving performance.
Which of the following are ways to improve performance in Power Query? (Multiple Select)
- a) Use native database query
- b) Disable the “Allow Native Database Query” option
- c) Use filters to load only the required data
- d) Increase the amount of data being loaded
Answer: a, c
Explanation: By using the native database query and applying filters, we can reduce the amount of data being loaded, hence improving the performance. Disabling the “Allow Native Database Query” option and increasing the amount of data being loaded would likely have the opposite effect.
True or False: Enable Load option in Power Query improves performance.
- Answer: False
Explanation: Disabling the ‘Enable load’ option makes sure that the query only works as a connection and its output is not loaded into the model, which supports memory saving and improves performance.
What does the option “Allow Native Database Query” do in Power Query?
- a) It allows the user to write SQL queries
- b) It prevents the user from writing SQL queries
- c) It improves the performance of Power Query
- d) It reduces the performance of Power Query
Answer: a
Explanation: “Allow Native Database Query” option allows the user to write SQL queries, which can help to reduce the size of the loaded data, hence improving performance.
True or False: Reducing the amount of loaded data can help improve the performance of Power Query.
- Answer: True
Explanation: Reducing the amount of loaded data helps to decrease the amount of memory used, which can increase the efficiency and speed of Power Query.
Which of the following is not a good practice to improve Power Query’s performance?
- a) Using filters excessively
- b) Consolidating queries to reduce number of steps
- c) Using the “Allow Native Database Query” option
- d) Reducing the amount of loaded data
Answer: a
Explanation: Overuse of filters can overload the system and actually reduce performance. The rest of the options can enhance Power Query’s speed and efficiency.
True or False: Removing unnecessary columns in data can help improve Power Query’s performance.
- Answer: True
Explanation: Removing unnecessary columns decreases the volume of data being processed which can dramatically improve performance in Power Query.
In Power Query, an alternative way to implement performance improvements is:
- a) Load more data
- b) Write complex queries
- c) Disable ‘Enable Load’
- d) Use many filters
Answer: c
Explanation: Disabling ‘Enable Load’ is an alternative performance enhancement in Power Query as this ensures the query only works as a connection and doesn’t load its output into the model, saving memory.
True or False: Using “Keep Rows” to filter down the data size in Power Query does not help in improving performance.
- Answer: False
Explanation: Using “Keep Rows” significantly reduces the size of the data and allows the system to process less data, improving performance.
True or False: Expanding all columns in Power Query can improve performance.
- Answer: False
Explanation: Expanding all columns increases the volume of data being processed, which can negatively impact the performance. It’s better to only expand necessary columns.
Which of these does not support saving memory in Power Query?
- a) Applying multiple filters
- b) Removing unnecessary columns
- c) Disabling ‘Enable Load’
- d) Writing complex queries
Answer: d
Explanation: Writing complex queries can overload the system and negatively impact performance instead of saving memory. Other options help to save memory by reducing the data that needs to be processed.
True or False: Using the power of databases to process data before getting into Power Query can improve performance.
- Answer: True
Explanation: The processing power of databases can be utilized to process and filter data before it gets to Power Query. This can decrease the data volume and lead to improvements in performance.
Is increasing the data privacy level in Power Query a good method to optimize performance?
- Answer: False
Explanation: Increasing the data privacy level actually reduces the ability for Power Query to optimize queries, affecting the performance negatively.
Which of the following can improve query performance in Power BI? (Multiple Select)
- a) Using variables
- b) Caching
- c) Writing complex DAX queries
- d) Using simpler DAX formulae
Answer: a, b, d
Explanation: Using variables, caching and simpler DAX formulae can all improve the performance of queries in Power BI. Complex DAX formulas can be harder to calculate, hence degrading performance.
Utilizing Query Diagnostics in Power Query is a useful way to?
- a) Optimize performance
- b) Increase data size
- c) Decrease security
- d) Avoid errors
Answer: a
Explanation: Query Diagnostics in Power Query can be used to identify and resolve performance issues by giving insights into the Power Query workload, thus helping to optimize performance.
Interview Questions
What are some ways to improve the performance of Power Query in Microsoft Power BI?
You can improve the performance of Power Query by reducing the amount of data it needs to process. This can be achieved by filtering data at source, using sample mode during the development, avoiding load of entire tables when only specific columns are needed, minimizing the use of transformations, optimizing the order of transformations.
How does filtering data at the source level improve the performance of Power Query?
When you filter data at the source level in Power Query, you reduce the amount of data that needs to be loaded into Power Query. This decreases memory usage and improves performance by limiting the amount of data that has to be processed.
Why is it beneficial to limit the number of columns in Power Query when trying to improve performance?
By limiting the number of columns in Power Query, you reduce the volume of data that the query needs to handle, which can significantly improve performance. It is always recommended to only load the columns needed for analysis.
Can using native database query within Power Query affect performance, and how?
Yes, using a native database query within Power Query can affect performance. The database server performs the query processing rather than Power Query, often resulting in a faster data retrieval. However, it’s suggested to be mindful of potential security implications surrounding the use of native SQL queries.
How do calculated columns in Power BI affect performance?
Calculated columns in Power BI consume memory and CPU because they are calculated during the data loading process. This means that every time data gets updated, these calculations run again, potentially slowing down the overall performance.
Why is it important to avoid using the ‘Change Type’ transformation excessively in Power Query?
Each ‘Change Type’ transformation triggers a Scan operation, which can increase overall query execution time. Using it excessively can lead to longer data loading times, affecting performance.
How does optimizing the sequence of transformations improve Power Query performance?
When transformations are executed in an optimized sequence, it can reduce the overall amount of data processing in Power Query. Ideally, you should first execute transformations that reduce the amount of data, like filtering rows, and then perform other transformations.
What is Query Folding in Power Query and how does it impact performance?
Query Folding is a technique where Power Query pushes back the data processing workload to the source database by generating SQL queries that represent the transformations. This improves performance by reducing the amount of data that needs to be processed by Power Query.
Why is it suggested to disable the ‘Auto Date/Time’ option in Power BI for the improved performance of Power Query?
The ‘Auto Date/Time’ option could lead to unnecessary data loading and processing as it automatically creates a hidden date table for each date type column. Disabling this option can help optimize Power Query’s performance.
How does the use of ‘Merge Queries’ impact the performance of Power Query?
‘Merge Queries’ can result in a lot of data being loaded into memory if large datasets are involved. This can slow down the performance of Power Query. It is advisable to filter data as much as possible before using ‘Merge Queries’.
How can ‘Parameterization’ in Power Query improve performance?
Parameterization can improve performance by enabling Query Folding. It allows the query to dynamically adjust according to the input parameters and perform computations at the source database level, which can result in faster data load times.
In Power Query, how does refreshing data from the source affect performance?
Refreshing data from the source in Power Query can introduce a significant load, affecting performance, especially if the data size is large. It is recommended to set appropriate refresh intervals to balance between data latency and performance.
How does using ‘Sample Mode’ during development improve Power Query performance?
Using ‘Sample Mode’ during development improves Power Query performance by reducing data size. It allows you to work with a representative subset of data during development which limits data processing, thereby speeding up query performance.
How can setting appropriate data privacy levels improve the performance of Power Query?
Setting appropriate data privacy levels lets Power Query know it can securely combine data from multiple sources, allowing it to perform more Query Folding. This move can significantly improve performance.
Can the ‘Buffer’ function help in improving Power Query performance?
Yes, in some cases, the ‘Buffer’ function can help improve Power Query performance. It allows Power Query to remember a table in memory, which can speed up subsequent data transformations that reference this table. However, it can increase memory consumption, so should be used judiciously.
extutorials.com