Tuesday 2 January 2024

What are some common Power Query/Query Editor transforms ? Power BI interview questions and answers 239

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.

No comments:

Post a Comment

Note: only a member of this blog may post a comment.