Cleaning and transforming data is an integral part of data analysis, as it helps in ensuring that your data is ready to be used efficiently for all sorts of data-based operations, including but not limited to, analysis, visualization, machine learning, etc. Power Query, commonly known as a data connection technology, allows you to discover, connect, combine, and refine data across multiple data sources with great ease for business intelligence.
1. Loading the Data into Power Query
Start by loading your data into Power Query. This data could be from a local file, database, or an online service. Power Query provides support for various file types and sources.
2. Inspecting Data
Once your data is loaded, the first step is to inspect it thoroughly. You should understand the structure of this data, which columns are critical for analysis, what kinds of values are in these columns, and if there are any anomalies or missing data.
3. Cleaning the Data
After detailing the data structure, the cleaning process starts. During this process, you must:
- Remove Unwanted Rows/Columns: Use the ‘Remove Columns’ or ‘Remove Rows’ option in Power Query to get rid of data that is irrelevant to your analysis.
- Fill or Remove Missing Values: The ‘Replace Values’ option can be used to replace missing/NULL values. You have the option of replacing these missing values with ZEROs, averages, or maximums depending on the context. Alternatively, you can also remove these rows entirely.
- Manage Duplicates: Use the ‘Remove Duplicates’ feature in Power Query to eliminate any duplicate data sets in your columns or rows to create a standardized data set.
4. Transforming the Data
Following data cleaning, it’s time to transform the data. The goal is to mold your data into a structure that is suitable for your analysis. This step includes:
- Change Data Types: You might need to adjust the data types of some columns for proper analysis. You can do this by simply selecting the column and changing its type from the ‘Transform’ ribbon.
- Creating New Columns: You may need to derive new columns based on existing ones. Power Query provides a variety of standard functions and operations that you can apply to your columns to derive new data.
- Pivoting and Unpivoting: For better analysis, you might need to rotate/transpose your data. Power Query provides two simple buttons ‘Pivot Column’ and ‘Unpivot Columns’ for this operation.
5. Load the Clean and Transformed data
Once your data is cleaned and transformed, you can load it back into your desired output location. Power Query offers numerous options for loading data, ranging from Excel Workbooks, Power BI, SQL Server, and more.
Here is a simple example of replacing null values with ZERO and renaming a column:
// Load the data
let
source = Csv.Document(File.Contents(“C:\temp\data.csv”),[Delimiter=”,”, Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
renamedColumns = Table.RenameColumns(source,{{“Column1”, “Year”}, {“Column2”, “Month”}, {“Column3”, “Sales”}}),
// Replace null values with ZERO
replacedNulls = Table.ReplaceValue(renamedColumns, null, 0, Replacer.ReplaceValue, {“Sales”})
in
replacedNulls
In conclusion, Power Query is a robust tool for cleaning and transforming data. It abstracts the complexities, providing a highly visual and intuitive interface for data preparation. As part of the preparation for the PL-900 Microsoft Power Platform Fundamentals exam, understanding the operations possible through Power Query is essential.
Practice Test
True or False: Power Query can be used to clean and transform data in Microsoft Power Platform.
- True
- False
Answer: True
Explanation: Power Query is a data connection tool that allows you to discover, connect, combine, and refine data across a wide variety of sources.
What is the primary tool used to clean and transform data in Power BI?
- a) Power Automate
- b) Power Apps
- c) Power Query
- d) Power Pivot
Answer: c) Power Query
Explanation: Power Query is a business intelligence tool available in both Excel and Power BI that allows you to import data from various sources and clean/transform it.
True or False: Power Query can only filter and sort data, but it can’t remove duplicates.
- True
- False
Answer: False
Explanation: Power Query can do various types of data transformations including filtering, sorting, and removing duplicates.
When using Power Query, in which step should data be cleaned?
- a) After the data has been loaded into the data model
- b) Before the data has been loaded into the data model
- c) After creating a visual
- d) Before creating a visual
Answer: b) Before the data has been loaded into the data model
Explanation: To ensure accuracy of the results, data should be cleaned before it is loaded into the data model.
True or False: You can join tables from different databases in Power Query.
- True
- False
Answer: True
Explanation: Power Query allows you to join tables from different databases, enabling you to create a comprehensive data model.
Which activity is NOT a typical use of Power Query?
- a) Merging datasets
- b) Pivot and unpivot data
- c) Creating advanced visuals
- d) Replace value
Answer: c) Creating advanced visuals
Explanation: While Power Query can transform data to facilitate visual creation, it’s not used directly to create advanced visuals. This would be done in Power BI or another visualization tool.
Multiple Select: Which of the following transformations can be performed with Power Query?
- a) Transpose
- b) Remove rows
- c) Replace values
- d) Change data type
- e) Conduct sentiment analysis
Answer: a) Transpose, b) Remove rows, c) Replace values, d) Change data type
Explanation: Power Query can perform all these transformations. However, conducting sentiment analysis is a more advanced analytical task and is not within the core functionality of Power Query.
True or False: Power Query is capable of handling both structured and unstructured data.
- True
- False
Answer: True
Explanation: Power Query can handle both structured and unstructured data from various sources.
Which of the following statements is TRUE about Power Query?
- a) It can pull data only from Excel files
- b) It supports data sources such as SQL Server, Web, Excel, and SharePoint
- c) Its M language can’t be customized for more complex data transformations
- d) It can’t handle large data.
Answer: b) It supports data sources such as SQL Server, Web, Excel, and SharePoint
Explanation: Power Query supports a wide range of data sources, including but not limited to SQL Server, Web, Excel, and SharePoint.
True or False: Power Query can perform transformations on a selected dataset directly without loading it into memory.
- True
- False
Answer: False
Explanation: Power Query loads data into memory before performing transformations. This provides an optimized and efficient data manipulation process.
Which Power Query tool is used for shaping and combining data from multiple sources?
- a) Merge
- b) Extract
- c) Append
- d) Split
Answer: a) Merge
Explanation: The Merge operation is used in Power Query to shape and combine data from multiple sources.
Interview Questions
What is Power Query in the context of data manipulation in Microsoft Power Platform?
Power Query is a data connection technology that allows you to discover, connect, combine, and refine data across a variety of sources, including local files, databases, cloud services, and more. It is a powerful tool used for data cleaning and transformation in Microsoft Power Platform.
What is the first step in cleaning and transforming data using Power Query?
The first step is to connect to your data source. Power Query supports a variety of sources, such as databases, Excel files, text files, cloud-based data, etc.
Can you remove columns from your data set in Power Query?
Yes, Power Query allows you to remove unwanted columns from your data set. Simply select the column and choose “Remove Columns” from the Home tab.
Can you perform calculations on your data in Power Query?
Yes, Power Query allows you to add new columns based on calculations from existing data. You can use the “Add Column” feature to perform these calculations.
How do you handle null or missing data in Power Query?
Power Query provides several ways to handle null or missing data. You can replace null values with a specific value, remove rows containing null values, or fill null values with values from adjacent cells.
Can you change data types of a column in Power Query?
Yes, you can change the data type of a column in Power Query. Just select the column, go to the “Transform” tab, and select “Data Type”.
What does the “Merge Queries” function do in Power Query?
“Merge Queries” function combines data from two queries into one, matching rows based on a shared key value. It’s similar to SQL JOIN operations.
How can you filter data in Power Query?
You can filter data in Power Query by selecting the filter icon in the column header and then choosing the filter condition.
Can Power Query handle duplicate data?
Yes, Power Query can detect and eliminate duplicate data. You can use the “Remove Duplicates” function to find and remove duplicate rows.
How can Power Query transform date and time data?
Power Query provides several functions to transform date and time data, such as extracting the day, month, or year, calculating the duration between two dates, or changing time zones.
How do you rename a column in Power Query?
To rename a column in Power Query, simply double click on the column header and input the new name.
Can you use Power Query to split a column of data into multiple columns?
Yes, Power Query provides a function called “Split Column” that allows you to divide a column of data into multiple columns based on a delimiter.
What does the “Group By” function do in Power Query?
The “Group By” function is used to group rows that have the same values in specified columns into aggregated results, like count, average, max, min, etc.
How can you load your cleaned and transformed data into Power BI using Power Query?
Once you have cleaned and transformed your data, you can load it into Power BI by selecting ‘Close & Apply’ from the Home tab in Power Query Editor.
Is it possible to automate the cleaning and transforming process in Power Query?
Yes, Power Query provides a feature called “Query Steps” that records each action you perform on the data. These steps can be automated and applied to future data sets.
extutorials.com