Data Types and Column Operations
Learn how to assign correct data types, rename columns, remove unnecessary fields, reorder columns, replace values, split text, and perform essential column operations in Power Query Editor.
1. Understanding Data Types
A data type defines how Power Query interprets the values stored in a column. Correct data types are important for calculations, date filtering, sorting, relationships, and report visuals.
Common Data Types in Power Query
| Data Type | Example | Typical Purpose |
|---|---|---|
| Text | Samiksha | Names, categories, codes, and descriptions. |
| Whole Number | 125 | Counts and integer quantities. |
| Decimal Number | 125.75 | Measurements and values requiring decimal precision. |
| Fixed Decimal Number | 125.50 | Fixed-precision numeric scenarios, including many currency calculations. |
| Date | 2026-10-22 | Calendar dates without a time component. |
| Date/Time | 2026-10-22 14:30 | Values containing both date and time. |
| Time | 14:30:00 | Time of day. |
| True/False | True | Logical conditions and flags. |
Visual 1: Different columns require different data types according to their purpose.
2. How to Change a Column Data Type
Power Query can automatically detect data types, but automatic detection may not always match your requirements. Review important columns and assign the appropriate type explicitly.
Steps to Change a Data Type
- Open Power BI Desktop and select Home → Transform Data.
- Select the required query in Power Query Editor.
- Select the column you want to change.
- Use the data type icon in the column header or the Data Type option on the ribbon.
- Choose the appropriate data type.
- If prompted, select the option to replace the existing type conversion or add a new conversion step.
- Inspect the preview for errors or unexpected values.
- Select Close & Apply when the data is ready.
Example: Correcting Sales Data Types
| Column | Incorrect Type | Recommended Type |
|---|---|---|
| OrderDate | Text | Date |
| Quantity | Text | Whole Number |
| UnitPrice | Text | Decimal or fixed decimal, depending on requirements. |
| CustomerID | Whole Number | Text if used as an identifier, especially when leading zeros matter. |
3. Renaming Columns
Column names should be clear, consistent, and meaningful to report users. Names such as TotalSales, OrderDate, and ProductCategory are usually easier to understand than Column1 or Field3.
Steps to Rename a Column
- Open Power Query Editor.
- Locate the column that needs a better name.
- Double-click the column heading, or right-click it and select Rename.
- Enter the new column name.
- Press Enter and verify the result.
Visual 2: Meaningful column names make data preparation and report maintenance easier.
4. Removing and Reordering Columns
A source dataset may contain many fields that are not needed for analysis. Removing irrelevant columns can simplify the query and make the dataset easier to maintain.
Removing Columns
- Select the column or columns that are not required.
- Right-click and select Remove Columns, or use the Remove Columns command on the ribbon.
- Review the remaining columns to ensure that important information has not been removed.
Reordering Columns
- Select a column heading.
- Drag it to the desired position, or use the available Move commands.
- Arrange the columns in a logical order for easier inspection.
Example: Simplifying an Orders Table
Original columns:
After removing an unused field and reordering:
Remove InternalNote only if it is genuinely unnecessary for reporting, auditing, or future analysis.
5. Replacing Values in a Column
Replace Values helps standardize known inconsistencies in a column. It is useful when the source contains alternate labels, outdated category names, or text values that should follow a consistent rule.
Steps to Replace Values
- Select the column that contains the values to correct.
- Right-click and select Replace Values, or use the appropriate ribbon command.
- Enter the value to find.
- Enter the replacement value.
- Confirm the operation and inspect the resulting values.
| Original Value | Replacement | Reason |
|---|---|---|
| W | West | Standardize a known region code. |
| N | North | Use a consistent region label. |
| Unknown | Unclassified | Use an agreed business label where appropriate. |
6. Splitting a Column
Split Column divides the contents of one column into multiple columns. It can split text using a delimiter, a character position, or other supported rules.
Example: Split a Full Name
Original column:
After splitting by a space delimiter:
Steps to Split a Column
- Select the text column.
- Open the Transform or Home tab in Power Query Editor.
- Select Split Column.
- Choose By Delimiter or another appropriate split method.
- Choose the delimiter and splitting options.
- Confirm the operation and rename the resulting columns if needed.
- Check rows with missing delimiters or multiple delimiters.
Visual 3: Splitting by a delimiter creates separate columns from text values.
7. Trimming, Cleaning, and Formatting Text
Text fields may contain extra spaces or non-printing characters that interfere with grouping and matching. Power Query provides text transformation commands to help normalize such values.
| Operation | Purpose | Example |
|---|---|---|
| Trim | Remove leading and trailing spaces. | " West " becomes "West". |
| Clean | Remove non-printing characters where supported. | Remove unwanted control characters. |
| Uppercase | Convert text to uppercase. | "west" becomes "WEST". |
| Lowercase | Convert text to lowercase. | "WEST" becomes "west". |
| Capitalize Each Word | Capitalize the first character of each word. | "central region" becomes "Central Region". |
8. Understanding Column Operations in Power Query
Column operations are used to reshape and prepare data. The correct operation depends on the structure of the source and the desired reporting result.
| Operation | What It Does | Example Use |
|---|---|---|
| Rename | Changes a column's label. | Field1 to CustomerName. |
| Remove | Deletes selected columns from the query output. | Remove unused comments. |
| Duplicate Column | Creates another column based on the selected column. | Keep the original date and create a separate transformation. |
| Split Column | Separates one column into multiple columns. | Split a code into prefix and number. |
| Replace Values | Replaces matching values according to the operation's rules. | Standardize a known category. |
| Change Type | Changes how values are interpreted. | Convert a text amount into a numeric value. |
| Extract | Creates a result from selected parts of a text value. | Extract text before a delimiter. |
9. Practical Exercise: Prepare an Orders Dataset
Scenario: An organization receives an orders file containing inconsistent names, text-formatted dates, and unnecessary fields.
Sample Data
Tasks
- Import the dataset into Power BI Desktop.
- Open Power Query Editor.
- Rename the query to OrdersData.
- Remove InternalNote if it is not needed.
- Trim the Region column.
- Set OrderDate to Date using the correct locale if necessary.
- Set Amount to a suitable numeric type.
- Review CustomerName for inconsistent spacing.
- Rename any unclear column headings.
- Inspect Applied Steps and select Close & Apply.
Expected outcome: A consistently named and typed orders table that is ready for modeling and report development.
10. Knowledge Check
Question 1: Which data type is most appropriate for a calendar date without a time component?
A. Text only
B. Date
C. True/False
D. Binary
Question 2: Which operation removes leading and trailing spaces?
A. Merge
B. Append
C. Trim
D. Pivot
Question 3: Why might a customer ID need to be stored as text?
A. To make it a date
B. To preserve identifier formatting such as leading zeros
C. To convert it into a decimal
D. To sort it as a calendar
Question 4: Which operation separates a full name using a space delimiter?
A. Split Column
B. Remove Rows
C. Group By
D. Close & Apply
Question 5: What should you do after changing a column's data type?
A. Ignore conversion errors
B. Delete the query
C. Review the data preview for errors and unexpected values
D. Publish immediately without checking
View Answers
- B — Date.
- C — Trim.
- B — To preserve identifier formatting such as leading zeros.
- A — Split Column.
- C — Review the data preview for errors and unexpected values.
Summary
In this lesson, you learned how to select appropriate data types, rename columns, remove unnecessary fields, reorder columns, replace values, and split text in Power Query Editor.
You also explored text cleaning, practical column operations, and methods for validating transformation results before loading data into the Power BI model.