Transforming & Formatting Columns

When working with data, columns often need to be transformed (changed structurally) or formatted (changed in appearance or data type).

Think of it like this:

• Transforming = Changing the data itself
• Formatting = Changing how the data looks or behaves

All of this is done inside Power Query Editor

(Open: Home → Transform Data)

1️⃣ What is Column Transformation?

Transformation changes:

• Data type (text → number)
• Text case (john → John)
• Splitting columns
• Merging columns
• Extracting values
• Replacing values
• Pivoting / Unpivoting

2️⃣ Changing Data Types (VERY IMPORTANT)

If data types are wrong, calculations won’t work properly.

Example (wrong type):

| Amount |
|--------|
| 200    |
| 300    |
| 400    |

If "Amount" is stored as Text, you cannot sum it correctly.

🔹 Change Data Type (GUI Method)

1. Select column
2. Click data type icon (ABC123 icon)
3. Choose:

• Whole Number
• Decimal Number
• Date
• Text
• Currency
• Percentage

🔹 M Code Example

= Table.TransformColumnTypes(Source,{
    {"Amount", type number},
    {"Order Date", type date},
    {"Customer", type text}
})

3️⃣ Transforming Text Columns

🔹 1. Change Text Case

Example:

| Customer |
|----------|
| john doe |
| JANE DOE |

Make Proper Case:

= Table.TransformColumns(Source,{
    {"Customer", Text.Proper}
})

Result:

| Customer  |
|-----------|
| John Doe  |
| Jane Doe  |

🔹 2. Trim & Clean Text

Remove extra spaces:

= Table.TransformColumns(Source,{
    {"Customer", Text.Trim}
})

Remove hidden characters:

= Table.TransformColumns(Source,{
    {"Customer", Text.Clean}
})

🔹 3. Replace Values

= Table.ReplaceValue(Source,
    "USA","United States",
    Replacer.ReplaceText,
    {"Country"}
)

4️⃣ Splitting & Merging Columns

🔹 Splitting Columns

Example:

| Full Name |
|-----------|
| John Doe  |

Split into:

= Table.SplitColumn(Source,
    "Full Name",
    Splitter.SplitTextByDelimiter(" "),
    {"First Name","Last Name"}
)

🔹 Merging Columns

Example:

| First Name | Last Name |
|------------|----------|
| John       | Doe      |

Merge:

= Table.CombineColumns(Source,
    {"First Name","Last Name"},
    Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),
    "Full Name"
)

5️⃣ Formatting Numbers

Formatting controls appearance.

Example:

• 2000 → $2,000
• 0.25 → 25%

In Power Query:

• Change to Currency
• Change to Percentage
• Set decimal places

🔹 Convert to Percentage

If column has 0.25:

= Table.TransformColumnTypes(Source,{
    {"Discount", Percentage.Type}
})

6️⃣ Formatting Dates

Dates must be correct type to:

• Sort properly
• Use in time analysis
• Create year/month columns

🔹 Extract Year

= Table.AddColumn(Source,
    "Year",
    each Date.Year([Order Date]),
    Int64.Type
)

🔹 Extract Month Name

= Table.AddColumn(Source,
    "Month",
    each Date.MonthName([Order Date]),
    type text
)

7️⃣ Creating Conditional Columns

Example:

| Amount |
|--------|
| 200    |
| 800    |

Create category:

= Table.AddColumn(Source,
    "Category",
    each if [Amount] > 500 then "High" else "Normal"
)

8️⃣ Pivot & Unpivot (Reshaping Data)

🔹 Unpivot Columns

Example:

| Product | Jan | Feb |
|---------|-----|-----|
| A       | 100 | 200 |

Unpivot converts to:

| Product | Month | Sales |
|---------|-------|-------|
| A       | Jan   | 100   |
| A       | Feb   | 200   |

M Code:

= Table.UnpivotOtherColumns(Source,
    {"Product"},
    "Month",
    "Sales"
)

9️⃣ Full Practical Example

Raw Data:

| OrderID | Customer Name | Order Date | Amount |
|--------|--------------|------------|--------|
| 1001   | john doe     | 1/2/23     | 200    |
| 1002   | JANE DOE     | 1/3/23     | 800    |

Full Transformation Script

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

    // Change data types
    ChangedType = Table.TransformColumnTypes(Source,{
        {"OrderID", Int64.Type},
        {"Customer Name", type text},
        {"Order Date", type date},
        {"Amount", type number}
    }),

    // Format text
    ProperNames = Table.TransformColumns(ChangedType,{
        {"Customer Name", Text.Proper}
    }),

    // Add Year column
    AddYear = Table.AddColumn(ProperNames,
        "Year",
        each Date.Year([Order Date]),
        Int64.Type
    ),

    // Add Sales Category
    AddCategory = Table.AddColumn(AddYear,
        "Category",
        each if [Amount] > 500 then "High" else "Normal"
    )

in
    AddCategory