Query transformation refers to the process of altering the way a query retrieves data to improve efficiency, performance or meet specific requirements. It can include restructuring, adding or removing query steps, or changing the query language. In the context of Microsoft Power BI, you usually transform queries in the Power Query Editor.
Transforming a Query in Power BI
To open the Power Query Editor, you’ll navigate to the “Home” tab, then select “Edit Queries”. Once the editor is open, you can view the “Applied Steps” panel on the right side, where you can see all the steps that Power BI applied to load your data.
You can modify these steps, eliminate unwanted steps, or insert new steps to transform your data. It’s crucial to note that any alterations you make in the Power Query Editor don’t modify the original data source, just the instance of it in Power BI.
Let’s go through a couple of common transformation tasks.
Changing Data Types
At times, Power BI may not correctly infer the data type when it loads data. For example, it might classify numerical data as text, which can hinder calculations. To transform the data type, follow these steps:
- In the Power Query Editor, select the column you want to transform.
- Navigate to the “Transform” tab.
- In the “Data Type” dropdown, select the correct data type.
Power BI will now add a step to change the data type when it reloads the data.
Removing Unwanted Columns
It’s often necessary to remove columns from your data that isn’t useful for your analysis. Here’s how:
- Select the column or columns you want to remove.
- Go to the “Home” tab.
- Click on “Remove Columns.”
Power BI will add a step to remove these columns when it reloads the data.
M Language
Under the hood, Power BI uses a language called M to transform queries. Every step you apply in the Power Query editor translates to a line of M code. You can view and edit this code to gain greater control over the query transformation process.
To access the M code for a query, in the Power Query editor:
- Go to the “Home” tab.
- Click on “Advanced Editor.”
Here, you can view and modify the M code for your query. It requires a decent understanding of the M Language to alter the code directly.
Conclusion
Transforming queries is an essential skill for any Power BI Data Analyst. It can improve performance and ensure data quality, something crucial for reliable analysis.
For more detailed hands-on practice, consider creating a sample dataset and trying to transform queries yourself. Remember, hands-on experience is invaluable when preparing for the PL-300 exam. Each transformation you apply in the Power Query Editor adds a step to the Applied Steps area, translating to a line of M code that you can access and modify through the Advanced Editor for more control over the transformation process. Always refer to the reliable documentation of Power BI and M Language to learn more transformation possibilities.
Practice Test
In Microsoft Power BI, query transformation is the process of changing the structure of data to fit the model’s requirements.
- (A) True
- (B) False
Answer : (A) True
Explanation : This is true. Query transformations are necessary to ensure data models operate optimally and that the data meets the user’s reporting requirements.
During a query transformation in Power BI, the source data can be modified directly.
- (A) True
- (B) False
Answer: (B) False
Explanation: While you can manipulate the data during a query transformation, it does not impact the original source data. All transformations are only reflected inside the Power BI environment.
Power Query transformation only supports flat tables or data sources.
- (A) True
- (B) False
Answer: (B) False
Explanation: Power Query supports a wide range of data structures, including non-relational sources, not just flat tables or Excel-like sources.
Which of the following operations can typically be included in query transformation?
- (A) Changing data types
- (B) Filtering rows
- (C) Merging columns
- (D) All of the above
Answer : (D) All of the above
Explanation : Query transformations can include a wide range of operations, including changing data types, filtering rows or columns, merging and splitting columns, removing duplicates etc.
Query transformation can only be performed once.
- (A) True
- (B) False
Answer: (B) False
Explanation: Query transformation can be performed multiple times until the desired result is achieved. Each transformation adds a step that can be re-accessed or adjusted at any time.
What is a key advantage of query transformations in Power BI?
- (A) Reduced report loading time
- (B) Improved accuracy of data
- (C) Both A and B
- (D) None of the above
Answer : (C) Both A and B
Explanation : By performing query transformations, analysts can improve report loading times by removing unnecessary data and it helps in maintaining the accuracy of data by converting the data into the required formats.
You can easily undo a previously performed transformation in Power BI.
- (A) True
- (B) False
Answer: (A) True
Explanation: Yes, Power BI provides a Query Dependency view that enables you to track transformations and undo them if necessary.
The Power Query Editor in Microsoft Power BI allows you to visually transform data.
- (A) True
- (B) False
Answer: (A) True
Explanation: The Power Query Editor provides a user-friendly, visual approach to defining and editing transformations.
When transforming a query in Power BI, it’s usually best to only keep data that you will need for your analysis.
- (A) True
- (B) False
Answer : (A) True
Explanation : It’s generally recommended to only keep the data that you plan to use for your analysis. This not only improves performance by reducing the size of your data model but also makes it easier to manage your data.
Order of the transformations applied in query processing does not affect the outcome.
- (A) True
- (B) False
Answer: (B) False
Explanation: The order of transformations can significantly affect the outcome. So, it’s crucial to arrange transformations in the correct order.
Interview Questions
What does transforming a query in Power Query entail?
Transforming a query in Power Query refers to the process of shaping and cleaning data. This process could include removing unneeded columns or rows, splitting a column into multiple columns, or other manipulations to structure and clean data before loading it into Power BI.
How can you transform a query in Power Query editor in Power BI?
In the Power Query Editor in Power BI, transforming a query can be performed by using various commands found on the Home, Transform, and Add Column tabs in the editor. Users can apply transformations such as changing data types, removing columns or rows, merging queries, and more.
Can you undo the transformations applied on a query in Power Query?
Yes, you can undo the transformations in Power Query. These actions can be viewed and undone from the Applied Steps section on the side pane of the Power Query window.
Which language is used for scripting and custom transformations in Power Query?
The M formula language is used for scripting and creating custom transformations in Power Query.
How can you rename a column in Power Query?
To rename a column in Power Query, select the column header, and then go to Transform > Rename.
What function can you use to extract part of the text from a cell in Power Query?
You can use the Text.BetweenDelimiters function to extract part of a text from a cell in Power Query.
How do you merge two queries in Power Query?
To merge two queries in Power Query, you can select the Home tab, then the Combine dropdown, and choose Merge.
What is the purpose of the ‘Group by’ function in Power Query?
The ‘Group by’ function in Power Query is used to group rows that have the same values in specified columns into a single record.
Can you use Power Query to connect to multiple sources of data?
Yes, Power Query provides connectivity to a wide range of data sources from traditional databases, Excel files to cloud services, and more, allowing you to connect and transform multiple sources of data.
How do you split a single column into multiple columns in Power Query?
You can split a single column into multiple columns in Power Query by selecting the column, going to the Transform tab, and clicking ‘Split Column’. You can specify the delimiter based on which Power Query will split the column.
What is the function of the ‘Transform’ tab in Power Query Editor?
The ‘Transform’ tab in the Power Query Editor facilitates various data transformations in your queries including grouping rows, transposing data, pivoting or un-pivoting columns, and adjusting text, numbers, and dates.
How can you change the data type of a column in Power Query?
In Power Query, you can change the data type of a column by selecting the column, then going to the Transform tab and clicking on the Data Type option.
What is applied when you perform a ‘Replace values’ operation in Power Query?
When you perform a ‘Replace values’ operation in Power Query, it will replace all instances of a value in the selected column with a new value.
Can you create custom columns in Power Query?
Yes, you can create custom columns in Power Query using the ‘Add Custom Column’ functionality. Users can use this to add new columns to their data by specifying a custom expression based on existing columns.
What does “Refresh Preview” do in Power Query?
“Refresh Preview” in Power Query updates the preview window to reflect any changes you’ve made to the query steps.
extutorials.com