What are some common Power Query/Query Editor transforms ?
Here are some of the most common transforms used in Power Query/Query Editor to prepare and shape data:
1. Data Shaping:
Remove Rows: Eliminate unwanted rows based on conditions or criteria.
Keep Rows: Preserve specific rows that meet certain conditions.
Filter Rows: Filter data based on values in one or more columns.
Sort: Arrange data in ascending or descending order based on a column.
Group By: Group rows based on shared values and perform aggregations.
Pivot Column: Reshape data by rotating columns into rows or vice versa.
Unpivot Columns: Expand columns into rows for easier analysis.
2. Data Cleaning:
Remove Columns: Delete unnecessary columns.
Rename Columns: Assign meaningful names to columns.
Change Data Type: Convert data types (e.g., text to numbers, dates, etc.).
Fill: Fill empty cells with specific values or based on patterns.
Replace Values: Substitute specific values with others.
Split Column: Divide a column into multiple columns based on delimiters.
Merge Columns: Combine multiple columns into one.
3. Data Transformation:
Add Column: Create new columns based on calculations or transformations.
Custom Column: Define custom calculations using a formula language.
Conditional Column: Add columns based on conditional logic.
Extract: Extract parts of text strings or dates.
Combine Queries: Merge multiple queries into a single table.
Append Queries: Add rows from one query to another.
Merge Queries: Join tables based on matching columns.
4. Data Enhancement:
Format: Apply formatting to numbers, dates, text, or currencies.
Lowercase: Convert text to lowercase.
Uppercase: Convert text to uppercase.
Trim: Remove leading and trailing spaces from text.
Grouping: Group rows based on shared values for aggregations or analysis.
5. Advanced Transforms:
Transpose: Flip table orientation (rows to columns and vice versa).
Parse: Extract structured data from unstructured text using patterns.
Fill Down: Fill empty cells with values from the previous non-empty cell.
Fill Up: Fill empty cells with values from the next non-empty cell.
Group By: Perform aggregations and calculations within groups.
These transforms are applied through a visual interface or M language, enabling you to shape and clean your data effectively for meaningful analysis and visualization in Power BI.
Remove Rows: Eliminate unwanted rows based on conditions or criteria.
Keep Rows: Preserve specific rows that meet certain conditions.
Filter Rows: Filter data based on values in one or more columns.
Sort: Arrange data in ascending or descending order based on a column.
Group By: Group rows based on shared values and perform aggregations.
Pivot Column: Reshape data by rotating columns into rows or vice versa.
Unpivot Columns: Expand columns into rows for easier analysis.
Remove Columns: Delete unnecessary columns.
Rename Columns: Assign meaningful names to columns.
Change Data Type: Convert data types (e.g., text to numbers, dates, etc.).
Fill: Fill empty cells with specific values or based on patterns.
Replace Values: Substitute specific values with others.
Split Column: Divide a column into multiple columns based on delimiters.
Merge Columns: Combine multiple columns into one.
Add Column: Create new columns based on calculations or transformations.
Custom Column: Define custom calculations using a formula language.
Conditional Column: Add columns based on conditional logic.
Extract: Extract parts of text strings or dates.
Combine Queries: Merge multiple queries into a single table.
Append Queries: Add rows from one query to another.
Merge Queries: Join tables based on matching columns.
Format: Apply formatting to numbers, dates, text, or currencies.
Lowercase: Convert text to lowercase.
Uppercase: Convert text to uppercase.
Trim: Remove leading and trailing spaces from text.
Grouping: Group rows based on shared values for aggregations or analysis.
Transpose: Flip table orientation (rows to columns and vice versa).
Parse: Extract structured data from unstructured text using patterns.
Fill Down: Fill empty cells with values from the previous non-empty cell.
Fill Up: Fill empty cells with values from the next non-empty cell.
Group By: Perform aggregations and calculations within groups.
No comments:
Post a Comment
Note: only a member of this blog may post a comment.