Formatting Currency

Formatting currency correctly is very important in data analysis because:

• It makes reports professional
• It improves readability
• It prevents calculation mistakes
• It ensures financial accuracy
• It helps stakeholders clearly understand values

Think of it like this:

Raw numbers = data

Formatted currency = clear financial information

1️⃣ What Does “Formatting Currency” Mean?

Formatting currency means displaying numeric values as money.

Example (raw data):

| Amount |
|--------|
| 2000   |
| 3500   |
| 1250   |

After formatting as currency:

| Amount |
|--------|
| $2,000.00 |
| $3,500.00 |
| $1,250.00 |

The value hasn’t changed — only how it looks.

2️⃣ Important Rule: Keep Currency as Numbers

Currency values must always remain numeric.

Wrong way (bad practice):

| Amount |
|--------|
| "$2000" |

That turns numbers into text, and then:

• You cannot sum correctly
• You cannot average
• You cannot build proper financial models

3️⃣ Step 1: Ensure Correct Data Type

Before formatting, the column must be:

• Whole Number
• Decimal Number
• Fixed Decimal Number (recommended for finance)

🔹 Convert to Numeric (Power Query)

= Table.TransformColumnTypes(Source,{
    {"Amount", type number}
})

If you are working with financial reports, use Fixed Decimal:

= Table.TransformColumnTypes(Source,{
    {"Amount", Currency.Type}
})

Currency.Type reduces floating-point rounding errors.

4️⃣ Rounding Currency Values

Most financial data uses 2 decimal places.

Example raw data:

| Amount |
|--------|
| 2000.456 |
| 3500.789 |

Round to 2 decimals:

= Table.TransformColumns(Source,{
    {"Amount", each Number.Round(_, 2), type number}
})

Result:

| Amount |
|--------|
| 2000.46 |
| 3500.79 |

5️⃣ Formatting Currency in Model View (Best Practice)

In Power BI:

1. Go to Data View
2. Select the column
3. Click Column Tools
4. Choose:

• Format → Currency
• Select currency symbol ($, €, £, etc.)
• Set decimal places

This changes appearance only — not the data itself.

6️⃣ Currency Symbols and International Reporting

Different countries use different currencies:

| Country | Sales |
|---------|-------|
| USA     | 2000  |
| UK      | 1500  |
| France  | 1800  |

If you need to assign symbols based on country:

= Table.AddColumn(Source,
    "Currency Symbol",
    each if [Country] = "USA" then "$"
         else if [Country] = "UK" then "£"
         else "€"
)

But actual formatting should still be handled in Model View or using DAX measures.

7️⃣ Currency Conversion Example

Suppose:

| SalesUSD |
|----------|
| 1000     |
| 2000     |

Exchange rate = 0.9 (USD to EUR)

= Table.AddColumn(Source,
    "SalesEUR",
    each [SalesUSD] * 0.9,
    type number
)

Then format SalesEUR as € currency.

8️⃣ Practical Full Example

Raw Data:

| OrderID | Amount |
|--------|--------|
| 1001   | 2000.456 |
| 1002   | 3500.789 |
| 1003   | 1200.1   |

Full Power Query Script

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

    // Ensure numeric type
    ChangedType = Table.TransformColumnTypes(Source,{
        {"OrderID", Int64.Type},
        {"Amount", Currency.Type}
    }),

    // Round to 2 decimal places
    RoundedAmount = Table.TransformColumns(ChangedType,{
        {"Amount", each Number.Round(_, 2), Currency.Type}
    })

in
    RoundedAmount

After loading:

• Format as Currency in Model View
• Choose symbol
• Set decimal places to 2

9️⃣ Common Beginner Mistakes

Adding "$" using text formula

Converting currency to text

Forgetting to set numeric data type

Not rounding properly

Mixing multiple currencies in one column

🔟 Best Practices Summary

| Step | What To Do |
|------|------------|
| 1 | Keep currency columns numeric |
| 2 | Use Currency.Type or Fixed Decimal |
| 3 | Round to 2 decimal places |
| 4 | Format in Model View |
| 5 | Avoid text-based currency symbols |

1️⃣1️⃣ Why Proper Currency Formatting Matters in Data Analysis

If currency is not formatted properly:

• Totals may be incorrect
• Financial dashboards look unprofessional
• Reports become confusing
• Rounding errors affect profit calculations
• Stakeholders may misinterpret results

In business, small formatting mistakes can lead to big misunderstandings.

COMPILATION OF ALL CODE BLOCKS (Single Combined Script)

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

    // Convert to proper numeric types
    ChangedType = Table.TransformColumnTypes(Source,{
        {"OrderID", Int64.Type},
        {"Amount", Currency.Type}
    }),

    // Round currency values
    RoundedAmount = Table.TransformColumns(ChangedType,{
        {"Amount", each Number.Round(_, 2), Currency.Type}
    }),

    // Add currency symbol column (optional)
    AddCurrencySymbol = Table.AddColumn(RoundedAmount,
        "Currency Symbol",
        each "$",
        type text
    ),

    // Example currency conversion
    AddConvertedCurrency = Table.AddColumn(AddCurrencySymbol,
        "Amount_EUR",
        each [Amount] * 0.9,
        Currency.Type
    )

in
    AddConvertedCurrency