Shaping Dates, Text, Numbers, and Business Fields

Introduction

Business intelligence depends not only on collecting data but also on expressing it in a form that supports reliable analysis. Source systems often contain dates stored as text, inconsistent product descriptions, currency symbols embedded in numeric fields, percentages represented in different ways, and business categories that have not yet been defined. Power Query provides a controlled environment for converting these raw values into analysis-ready attributes before data is loaded into the Power BI model.

This chapter examines practical transformation techniques for dates, times, durations, text, numbers, percentages, currencies, and conditional business fields. The emphasis is on preserving meaning, documenting assumptions, handling errors, and creating reusable transformations.

Learning Objectives

By the end of this chapter, you should be able to:

1. Distinguish among date, time, datetime, datetimezone, and duration values.
2. Clean and standardize text fields using Power Query.
3. Convert numeric, percentage, and currency values safely.
4. Create business classifications using conditional logic.
5. Apply locale-aware transformations and handle invalid values.
6. Evaluate transformation quality and governance implications.

1. The Role of Data Shaping

Data shaping is the process of changing the structure, type, format, and meaning of source data so that it can be analysed consistently. A transformation should answer a business need. For example:

• A transaction date may need year, quarter, month, and weekday attributes.
• A customer name may require trimming and standardisation.
• A revenue field may need conversion from text to decimal currency.
• A sales amount may be used to classify orders as Small, Standard, or Large.

A useful distinction is between presentation formatting and data transformation. Formatting changes how a value appears, whereas transformation changes its stored type or analytical meaning. Setting a decimal number to display as currency is different from removing a currency symbol from a text value and converting the result to a number.

Power Query transformations are generally applied before data enters the model. This supports a principle often expressed as “clean once, use many times”: a corrected field can then be used by reports, calculations, and downstream users.

2. Data Types and Type Safety

Power Query supports important primitive types including text, logical, number, date, time, datetime, datetimezone, and duration. Correct data types are essential because operations depend on them. A date can be grouped by month; a text representation of a date cannot be reliably grouped until it is converted.

Type conversion should occur as close as possible to the source-cleaning stage. However, conversion should be performed only after understanding the source format. For example, 03/04/2025 can mean 3 April or 4 March depending on locale.

A type conversion may produce errors when a value cannot be interpreted. Errors should not be silently replaced with zero or an empty string because such replacements can alter totals and conceal source-quality problems.

3. Working with Dates and Times

3.1 Date components

Date fields are commonly shaped into attributes such as year, month number, month name, quarter, day of month, day of week, and week number. Numeric month and weekday columns are particularly important for correct sorting. Sorting month names alphabetically produces April, August, December rather than calendar order; sorting by month number prevents this problem.

Power Query M examples include:

Date.Year([OrderDate])
Date.Month([OrderDate])
Date.MonthName([OrderDate])
Date.QuarterOfYear([OrderDate])
Date.DayOfWeekName([OrderDate])

A month label can be constructed as:

Date.ToText([OrderDate], "yyyy-MM")

This produces a sortable label such as 2025-04. If a report requires a user-friendly label such as Apr 2025, it is often better to retain a separate chronological sort column.

3.2 Date and time distinctions

A date answers “on which calendar day?” A time answers “at what time of day?” A datetime combines both. A duration answers “how long?” These concepts should not be confused. Subtracting two datetimes produces a duration, not another datetime.

For example, if a service ticket opens at 09:20 and closes at 12:05, the elapsed duration is 2 hours and 45 minutes. In minutes:

The calculation assumes both timestamps use the same time zone and that the ticket does not cross a daylight-saving transition.

Power Query operations include:

[ClosedAt] - [OpenedAt]
Duration.Days([Duration])
Duration.Hours([Duration])
Duration.TotalMinutes([Duration])

Duration.Hours returns the hour component, whereas Duration.TotalHours returns the complete duration expressed in hours. This distinction matters for intervals longer than one day.

3.3 Time zones and daylight saving

International organisations should not treat local timestamps as universally comparable. A datetimezone value includes an offset from Coordinated Universal Time (UTC). A recommended approach is to retain the original timestamp, record its time zone, and create a standard UTC field for cross-region analysis.

A timestamp without a zone may be ambiguous. For example, 09:00 in London and 09:00 in Singapore do not represent the same instant. Time-zone assumptions should be documented in the query or data dictionary.

3.4 Missing and invalid dates

Common date problems include blank dates, impossible dates, mixed formats, and placeholder values such as 1900-01-01. A blank delivery date may mean “not delivered,” whereas a placeholder may mean “unknown.” These meanings should not be collapsed into one value.

A robust design may create:

• DeliveryDate, containing valid dates or null;
• DeliveryStatus, identifying Delivered, Pending, or Invalid;
• DateQualityFlag, recording whether the original value was valid.

4. Text Transformation

Text quality affects grouping, joining, searching, and classification. Values that look similar to a person may be different to a computer. For example, London, london, and LONDON may be treated as separate categories unless standardised.

Common Power Query text functions include:

Text cleaning should be purposeful. Converting names to uppercase may support matching, but it may be inappropriate for customer-facing display. It is often useful to retain the original field and create a cleaned analytical field.

4.1 Standardising categories

Suppose a channel field contains Web, web, Online, and E-commerce. A simple case conversion is insufficient because the values have different labels for the same business concept. A mapping table is usually more maintainable than a long series of nested replacements.

A mapping table can contain:

The source value should first be trimmed and converted to a consistent case. It can then be joined to the mapping table. Unmatched values should be flagged rather than automatically assigned to a category.

4.2 Identifiers and leading zeros

Codes such as 001245 should normally remain text. Converting them to numbers removes leading zeros and may change the identifier. An identifier is not automatically a measure merely because it contains digits.

5. Numbers, Percentages, and Currency

5.1 Numeric conversion

Numeric values may arrive as text because of currency symbols, thousands separators, decimal separators, or system exports. Conversion should account for locale. For example, 1.234,56 commonly represents 1,234.56 in a comma-decimal locale, whereas 1,234.56 uses a point decimal separator.

Power Query allows locale-aware type conversion through the user interface and M functions such as:

Number.FromText([AmountText], "en-US")
Number.FromText([AmountText], "de-DE")

The locale must match the source convention. Applying the wrong locale can produce an incorrect value without necessarily generating an error.

5.2 Currency cleaning

A value such as $1,250.00 contains both a number and presentation characters. The analytical value is 1250.00, while the currency code should ideally be stored separately. A better source design uses fields such as Amount and CurrencyCode, rather than embedding $, €, or £ in the amount.

If currency symbols must be removed, the transformation should also address thousands separators and negative notation. Parentheses may represent negative values: (250.00) should become −250.00. These rules must be documented and tested.

Currency conversion is a separate business operation from currency cleaning. If an amount is converted, the organisation must specify the exchange-rate source, date of the rate, rate type, and rounding policy.

5.3 Percentages

Percentages are frequently represented inconsistently. A source value of 25% may be stored as text, while 0.25 may be stored as a decimal number. In analytical models, 25% is conventionally represented as 0.25 and formatted for display as 25.00%.

The relationship is:

Thus, if the displayed percentage is 25, the decimal proportion is 0.25. This conversion applies only when the source value is expressed in percentage points, not when it is already a decimal proportion.

A common error is dividing a value already stored as 0.25 by 100, producing 0.0025. Data profiling and source documentation are therefore essential.

5.4 Rounding and precision

Rounding can affect totals and margins. A display format may show two decimal places without changing the underlying value; a transformation that rounds the stored value does change it. Financial policy should specify whether rounding occurs at transaction level, invoice level, or reporting level.

6. Conditional Business Fields

Business fields convert raw values into decision-support categories. Examples include:

• Customer segment based on lifetime value;
• Order priority based on promised delivery date;
• Margin band based on gross margin percentage;
• Inventory status based on stock and reorder level.

A conditional field should have mutually exclusive rules, complete coverage, and a documented order of evaluation. In Power Query, conditions are evaluated from top to bottom. Therefore, more specific or restrictive conditions should generally appear before broad conditions.

Example M expression:

if [SalesAmount] >= 10000 then "Large"
else if [SalesAmount] >= 2500 then "Medium"
else "Small"

The assumptions are that SalesAmount is numeric, thresholds are expressed in the same currency, and the categories are intended to be inclusive at the lower boundary. Under these rules, 10,000 is Large and 2,500 is Medium.

A more complete classification should handle nulls explicitly:

if [SalesAmount] = null then "Unknown"
else if [SalesAmount] >= 10000 then "Large"
else if [SalesAmount] >= 2500 then "Medium"
else "Small"

Classification rules should be maintained as business logic, not hidden in a report visual. If thresholds change regularly, store them in a parameter or reference table.

6.1 Rule quality

A reliable classification has:

1. Validity: every output category has a defined meaning.
2. Completeness: all relevant input values receive an outcome.
3. Exclusivity: a value cannot unintentionally belong to two categories.
4. Stability: small irrelevant changes do not create unexpected categories.
5. Traceability: the rule and its owner are documented.

7. Applied Case: Regional Service Operations

Background

The fictional Northstar Services organisation receives service records from three regional systems. The records contain OpenedDate, ClosedDate, Region, Priority, ResolutionTime, CustomerType, and InvoiceAmount. Data is used to monitor response performance and segment customers.

Problem

The systems use different date formats. Resolution time appears in some files as text such as 02:30, while other files provide minutes. Region names contain inconsistent spaces and case. Invoice amounts use both European and US numeric conventions. Managers also require a classification of invoices as Basic, Standard, or Premium.

Analysis

The analyst profiles each source and identifies the following risks:

• Date interpretation may change by locale.
• A duration stored as 02:30 could be mistaken for a time of day.
• Currency conversion may be confused with currency formatting.
• Unmatched region names may cause incorrect joins.
• Classification thresholds must be applied consistently.

Approach and solution

First, the original fields are retained for audit purposes. Date columns are converted using the known locale of each regional source. Region is trimmed and standardised, then matched to a reference table. Resolution records are converted to durations, and a total-minutes field is created. Invoice amounts are converted using the relevant locale, while currency codes remain separate.

The business classification is defined as follows:

Null or invalid amounts are assigned Unknown, not Basic. A quality report counts invalid dates, unmatched regions, and unknown invoice classes.

Outcome

The resulting dataset supports monthly service analysis, regional comparisons, and customer segmentation. Because the original values and quality flags remain available, managers can distinguish operational performance from data-quality problems.

Learning points

The case demonstrates that shaping is not merely cosmetic. Correct types, locale-aware parsing, explicit duration logic, reference mappings, and documented classifications directly influence the reliability of business decisions.

8. Power Query Implementation Practices

The Power Query interface is useful for applying transformations, but the underlying M steps should also be reviewed. Good practice includes:

• Rename steps descriptively rather than leaving generic names.
• Place source-specific cleaning near the source stage.
• Keep type changes explicit.
• Avoid unnecessary repeated transformations.
• Use parameters for file paths, thresholds, and locales.
• Create reference queries for reusable mappings.
• Preserve source columns where auditability matters.
• Test nulls, blanks, invalid values, negative values, and boundary values.
• Refresh using representative samples and full production data.

A transformation should be idempotent where practical: applying the intended process again should not progressively damage the value. For example, a carefully designed standardisation step should not repeatedly add text or alter an already-clean code.

9. Challenges, Governance, and Ethical Considerations

Data shaping can introduce bias or conceal important distinctions. A classification such as “high-value customer” may influence service priority, credit decisions, or marketing access. The threshold and consequences should therefore be approved by the responsible business owner.

Governance considerations include:

• documenting source systems and locales;
• recording who owns transformation rules;
• protecting personal data in names, addresses, and contact fields;
• limiting access to sensitive attributes;
• retaining audit evidence for material transformations;
• validating automated classifications before operational use.

AI-assisted tools may suggest transformations or detect anomalies, but suggestions require human review. An automated tool may infer an incorrect locale, remove meaningful characters, or classify unusual records inaccurately. AI should assist profiling and documentation, not replace accountability for business definitions.

10. Best-Practice Checklist

Before publishing a shaped dataset, ask:

1. Are all columns assigned appropriate data types?
2. Are dates interpreted with the correct locale and time zone?
3. Are durations distinguished from times of day?
4. Are identifiers protected from loss of leading zeros?
5. Are numeric, percentage, and currency rules documented?
6. Are null, invalid, and placeholder values treated deliberately?
7. Are text categories standardised using an approved mapping?
8. Are business rules complete, mutually exclusive, and traceable?
9. Are quality flags and exception counts available?
10. Can another analyst understand and reproduce the transformation?

Chapter Summary

Power Query enables analysts to convert raw source values into reliable analytical attributes. Dates and times should be typed correctly, interpreted according to locale and time zone, and separated from durations. Text should be trimmed, cleaned, standardised, and mapped without damaging meaningful identifiers. Numeric, percentage, and currency values require explicit assumptions about separators, units, symbols, exchange rates, and rounding. Conditional business fields should use documented, complete, and mutually exclusive rules, with null and invalid values handled explicitly. High-quality shaping combines technical correctness with governance, auditability, and awareness of business consequences.

Key Terms

Data shaping: Transforming source data into a structure suitable for analysis.

Locale: Regional convention governing formats such as dates, decimals, and currency.

Date: A calendar day without a time component.

Time: A time of day without a calendar date.

DateTime: A combined date and time value.

DateTimeZone: A date and time associated with a UTC offset.

Duration: An elapsed interval between two points in time.

Type conversion: Changing a value from one data type to another.

Text.Trim: A Power Query operation that removes leading and trailing spaces.

Text.Clean: A Power Query operation that removes non-printing characters.

Locale-aware parsing: Interpreting values according to regional formatting rules.

Identifier: A code used to identify an entity, not normally a measure.

Percentage point: A value expressed directly on a 0–100 scale.

Decimal proportion: A percentage represented between 0 and 1.

Currency code: A standard code identifying a currency, such as USD or EUR.

Conditional classification: Assigning categories according to logical rules.

Reference table: A table used to map source values to approved values.

Data-quality flag: An attribute identifying a missing, invalid, or exceptional value.

Idempotent transformation: A transformation that does not progressively change a value when repeated.

Review Questions

Short Answer Questions

1. Why is assigning the correct data type important in Power Query?
2. What is the difference between a datetime and a duration?
3. Why should identifiers with leading zeros usually remain text?
4. What is locale-aware parsing?
5. Why should null invoice amounts not automatically be classified as Basic?

Long Answer Questions

1. Explain how dates, times, datetime values, and durations differ, using a service-operations example.
2. Discuss the risks of converting currency and percentage fields without documenting source conventions.
3. Describe a robust process for standardising text categories using a mapping table.
4. Explain how conditional business classifications should be designed and governed.
5. Discuss the role of quality flags, auditability, and human review in Power Query transformations.

Analytical / Application Questions

1. A source contains 03/04/2025, 1.234,50, and 25%. Identify the likely interpretation risks and propose a transformation plan.
2. Design Power Query logic to classify sales as Low, Medium, or High while handling nulls and boundary values.
3. A multinational company records local service timestamps without time zones. Analyse the consequences for performance reporting and recommend a remediation approach.

Multiple Choice Questions with Answers

Question 1

Which Power Query type is most appropriate for an elapsed interval between two timestamps?

A. Date

B. Time

C. Duration

D. Text

Correct Answer: C. Duration

Question 2

Why should a month number usually accompany a month name in an analytical table?

A. To increase the number of rows

B. To support chronological sorting

C. To convert text into currency

D. To remove duplicate dates

Correct Answer: B. To support chronological sorting

Question 3

What is the main risk of applying the wrong locale when converting numeric text?

A. The column becomes a date

B. The value may be interpreted incorrectly without an obvious error

C. All nulls become zero

D. Leading zeros are added

Correct Answer: B. The value may be interpreted incorrectly without an obvious error

Question 4

A source stores a percentage as 25. What decimal proportion does it represent if the source uses percentage points?

A. 0.025

B. 0.25

C. 2.5

D. 25.0

Correct Answer: B. 0.25

Question 5

Which field should normally remain text?

A. Order quantity

B. Invoice amount

C. Product code 001245

D. Gross margin

Correct Answer: C. Product code 001245

Question 6

In a top-to-bottom conditional expression, which condition should generally appear first?

A. The broadest condition

B. A condition that can never be true

C. The more specific or restrictive condition

D. A random condition

Correct Answer: C. The more specific or restrictive condition

Question 7

What is the best treatment for an unmatched value in a business mapping table?

A. Silently delete the row

B. Automatically assign the first category

C. Flag it for review

D. Convert it to zero

Correct Answer: C. Flag it for review

Question 8

If a ticket opens at 09:20 and closes at 12:05 on the same day, what is the elapsed time?

A. 145 minutes

B. 155 minutes

C. 165 minutes

D. 175 minutes

Correct Answer: C. 165 minutes

Question 9

Which operation removes leading and trailing spaces from a text value in Power Query?

A. Text.Trim

B. Text.Start

C. Text.Combine

D. Text.Contains

Correct Answer: A. Text.Trim

Question 10

Why is a currency code preferably stored separately from an amount?

A. To prevent all arithmetic

B. To distinguish monetary unit from numeric value

C. To convert every amount to zero

D. To remove the need for data types

Correct Answer: B. To distinguish monetary unit from numeric value