Power Query Editor offers a variety of tools for filtering and transforming data, making it easier to clean and process your data. Below is an overview of some important tools and their uses:
.jpg) |
| Data Filtering and Transformation Tools |
Data Filtering Tools
1. Filter Rows:
- Basic Filter:
Filter data in a column based on specific values or conditions. For example,
showing only rows with a particular value in a column.
- Advanced Filter:
Filter data based on multiple conditions, such as greater than, less than, or
dates after a specific point.
2. Keep Rows / Remove
Rows:
- Keep Top Rows:
Keep only the top N rows (e.g., the first 10 rows).
- Keep Bottom Rows:
Keep only the bottom N rows (e.g., the last 10 rows).
- Remove Top Rows:
Remove the top N rows.
- Remove Bottom
Rows: Remove the bottom N rows.
3. Remove Duplicates:
- Remove Duplicate
Rows: Remove duplicate rows based on specific columns.
Data Transformation
Tools
1. Change Data Type:
- Data Type: Change
the data type of a column (e.g., text, number, date).
2. Split Column:
- By Delimiter:
Split a column based on a specific delimiter (e.g., comma or space).
- By Number of
Characters: Split a column based on a specific number of characters.
3. Merge Columns:
- Combine Columns:
Merge multiple columns into one to create a new column.
4. Add Column:
- Custom Column:
Create a new column using custom formulas.
- Index Column: Add
an index column that automatically assigns a sequential number.
5. Group By:
- Group By: Group
data based on specific columns and apply aggregate functions at the group level
(e.g., average, sum).
6. Pivot Column:
- Pivot Column:
Create new columns based on values from one or more columns and pivot their
values.
7. Unpivot Columns:
- Unpivot Columns:
Convert multiple columns into a single column, which is the reverse of a pivot
operation.
8. Fill Down / Fill
Up:
- Fill Down: Fill
down cells in a column using the value from the above cell.
- Fill Up: Fill up
cells in a column using the value from the below cell.
9. Remove Columns:
- Remove Columns:
Remove unnecessary columns.
10. Replace Values:
- Replace Values:
Replace specific values with other values.
By using these tools, you can easily filter and transform
your data, making it more effective and accurate for analysis.
.jpg&description= Data Filtering and Transformation Tools - AnalysisWithSadiul){kind=link}