Dates, Time, Text and Business Logic

Introduction

Reliable managerial reporting depends not only on importing data, but also on interpreting fields correctly. Dates must represent the intended business events, times must respect time zones and operating hours, text must be consistently parsed, and business rules must be explicit and auditable. Power Query provides the transformation layer in which these requirements can be implemented before data reaches the Power BI data model.

This chapter develops practical techniques for working with dates, times, durations, text, conditional rules, fiscal periods and business classifications in Power Query. The examples use Power Query M, the functional language behind Power Query in Power BI.

Learning Objectives

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

• distinguish date, time, datetime and datetimezone values;
• convert values safely using locale-aware methods;
• construct calendar and fiscal-period attributes;
• calculate durations and service-level measures;
• clean, split, combine and classify text;
• implement conditional business logic with readable M expressions;
• identify common reliability, governance and performance risks.

1. Temporal Data Types and Their Meaning

A temporal value is useful only when its meaning is understood. The same character string may represent different dates in different regions. For example, 03/04/2025 means 3 April in a day-month-year convention and 4 March in a month-day-year convention.

Power Query commonly uses four temporal types:

A datetime does not identify a global instant unless the assumed time zone is documented. A datetimezone carries an offset, but the offset itself may not capture daylight-saving rules for future or historical conversions. Organisations should therefore document the source-system time zone and the reporting time zone.

Use explicit constructors when creating values:

#date(2025, 4, 3)
#time(14, 30, 0)
#datetime(2025, 4, 3, 14, 30, 0)
#datetimezone(2025, 4, 3, 14, 30, 0, 0, 0)

Type conversion should occur as early as practical, but only after the correct interpretation is known. A column containing ambiguous text should not be converted merely by clicking a default data-type button.

2. Locale-Aware Conversion

Locale controls conventions such as date order, decimal separators and month names. In Power Query, conversion functions can receive a culture code such as en-GB, en-US, fr-FR or de-DE.

For a text column named OrderDateText, a culture-aware conversion can be written as:

= Table.TransformColumnTypes(
    Source,
    {{"OrderDateText", type date}},
    "en-GB"
)

For more explicit control:

= Table.AddColumn(
    Source,
    "OrderDate",
    each Date.FromText([OrderDateText], "en-GB"),
    type date
)

The second approach is often preferable when the original column must be retained for audit purposes. If invalid records are possible, use try:

= Table.AddColumn(
    Source,
    "OrderDateResult",
    each try Date.FromText([OrderDateText], "en-GB")
)

The result is a record containing either a value or an error. A controlled pattern can separate valid and invalid rows:

= Table.AddColumn(
    Source,
    "OrderDate",
    each try Date.FromText([OrderDateText], "en-GB") otherwise null,
    type date
)

Replacing errors with null prevents refresh failure, but it does not solve the data-quality problem. A production solution should also create an exception report containing the original value, source identifier and error reason.

Culture and Numeric Text

The same principle applies to numbers. In a culture using a comma as the decimal separator, "12,50" may mean twelve and one-half. Conversion without culture can produce an error or an incorrect result. Locale should be treated as metadata, not as an incidental user-interface setting.

3. Calendars and Fiscal Periods

A calendar table provides a consistent vocabulary for time analysis. It normally contains one row per date and attributes such as year, month number, month name, quarter, week, working-day flag and fiscal period. Facts such as sales or tickets should relate to this table through a date key rather than each fact table implementing its own definitions.

A simple calendar can be generated in Power Query. The following example creates dates from 1 January 2023 to 31 December 2025:

let
    StartDate = #date(2023, 1, 1),
    EndDate = #date(2025, 12, 31),
    NumberOfDays = Duration.Days(EndDate - StartDate) + 1,
    Dates = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),
    Calendar = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
    Typed = Table.TransformColumnTypes(Calendar, {{"Date", type date}}),
    AddYear = Table.AddColumn(Typed, "Year", each Date.Year([Date]), Int64.Type),
    AddMonthNo = Table.AddColumn(AddYear, "MonthNo", each Date.Month([Date]), Int64.Type),
    AddMonth = Table.AddColumn(AddMonthNo, "Month", each Date.MonthName([Date], "en-GB"), type text),
    AddQuarter = Table.AddColumn(AddMonth, "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
    AddDayName = Table.AddColumn(AddQuarter, "DayName", each Date.DayOfWeekName([Date], "en-GB"), type text),
    AddWeekday = Table.AddColumn(AddDayName, "IsWeekday", each Date.DayOfWeek([Date], Day.Monday) < 5, type logical)
in
    AddWeekday

The expression Duration.Days(EndDate - StartDate) + 1 counts both endpoints. The + 1 is necessary because subtracting two dates gives the number of intervals between them, whereas a calendar requires the number of included dates.

Fiscal Years

A fiscal year may begin in a month other than January. Let FiscalStartMonth be the first month of the fiscal year, where January is 1 and December is 12. For a date with calendar year Y and month M, the fiscal year label can be defined as:

This convention labels a fiscal year by the year in which it ends. For an April start, April 2025 belongs to FY2026. Some organisations instead label it FY2025, so the naming convention must be agreed before implementation.

In M, for an April-start fiscal year:

= Table.AddColumn(
    PreviousStep,
    "FiscalYear",
    each if Date.Month([Date]) >= 4
         then Date.Year([Date]) + 1
         else Date.Year([Date]),
    Int64.Type
)

A more flexible method uses a parameter:

FiscalStartMonth = 4,
AddFiscalYear = Table.AddColumn(
    PreviousStep,
    "FiscalYear",
    each Date.Year([Date]) +
        (if Date.Month([Date]) >= FiscalStartMonth then 1 else 0),
    Int64.Type
)

Fiscal month number, numbered from 1 through 12, can be calculated as:

where M is the calendar month and S is the fiscal-start month. In M:

= Number.Mod(Date.Month([Date]) - FiscalStartMonth + 12, 12) + 1

The addition of 12 ensures a non-negative intermediate value for ordinary month inputs.

Week Definitions and Business Calendars

Week numbering varies by standard. A business may use ISO weeks, Sunday-based weeks or a retail 4-4-5 calendar. These definitions are not interchangeable. A calendar table should therefore include a documented week-system field and, where necessary, a separate retail calendar maintained by finance or operations.

Public holidays should be stored in a maintained holiday table rather than hard-coded into a query. A working-day flag can then be derived by checking both weekday status and holiday membership. This design supports changes in holiday calendars without rewriting transformation logic.

4. Duration Calculations and Service Levels

Subtracting two temporal values returns a duration. For example:

= [ResolvedAt] - [OpenedAt]

A duration contains days, hours, minutes and seconds. Useful extraction functions include Duration.Days, Duration.Hours, Duration.Minutes and Duration.TotalHours.

If a ticket is opened at 09:15 and resolved at 13:45 on the same day, the elapsed duration is 4 hours 30 minutes. Duration.TotalHours returns 4.5, which is suitable for numerical aggregation. Duration.Hours returns only the hour component and therefore returns 4; it should not be used when durations may exceed 24 hours.

A robust elapsed-hours column is:

= Table.AddColumn(
    PreviousStep,
    "ElapsedHours",
    each if [OpenedAt] = null or [ResolvedAt] = null
         then null
         else Duration.TotalHours([ResolvedAt] - [OpenedAt]),
    type number
)

The calculation assumes both values use the same time basis. If one value is local time and the other is UTC, the result may be wrong by the time-zone offset.

Business Hours

Elapsed time and business time are different measures. A ticket opened Friday at 16:00 and closed Monday at 10:00 may have 66 elapsed hours but far fewer working hours. Business-hour calculations require a defined schedule, such as Monday–Friday, 09:00–17:00, plus a holiday calendar. For complex requirements, a reusable function should iterate across dates, calculate the overlap with each working-day interval and sum the results.

The definition of a service-level agreement must specify:

• whether the clock starts at creation, assignment or acknowledgement;
• whether weekends and holidays are excluded;
• how pauses or customer waiting time are treated;
• which time zone applies;
• the rounding rule;
• the treatment of missing or invalid timestamps.

5. Text Preparation and Parsing

Text fields often contain identifiers, names, addresses, product descriptions and status labels. Text transformations should preserve meaning while eliminating irrelevant variation.

Common functions include:

A basic normalisation expression is:

= Table.TransformColumns(
    Source,
    {{"CustomerName", each Text.Proper(Text.Trim(Text.Clean(_))), type text}}
)

This is useful for presentation, but Text.Proper can damage legitimate names such as acronyms, particles or deliberately styled brands. Critical identity fields should generally be standardised using a reference table rather than automatic casing alone.

Delimiters and Position-Based Parsing

Suppose an identifier has the form UK-2025-00481. It can be split using a hyphen delimiter:

= Table.SplitColumn(
    Source,
    "ProductCode",
    Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),
    {"Country", "Year", "Sequence"}
)

Position-based extraction is appropriate when the format is fixed:

Text.Start([ProductCode], 2)
Text.Middle([ProductCode], 3, 4)
Text.End([ProductCode], 5)

Delimiter parsing is preferable when component lengths vary. Parsing rules should be tested against nulls, extra delimiters, missing components and unexpected characters.

Combining Text

A display label can be created as follows:

= Table.AddColumn(
    Source,
    "CustomerLabel",
    each Text.Combine({[CustomerID], [CustomerName]}, " - "),
    type text
)

If null values may occur, use a controlled list. For example, List.RemoveNulls({[CustomerID], [CustomerName]}) can be passed to Text.Combine. Concatenated labels are presentation attributes, not reliable keys.

6. Conditional Logic and Business Classification

Business logic translates operational attributes into managerial categories. A classification rule should be explicit, ordered and mutually understandable. In M, the primary structure is:

if condition1 then result1
else if condition2 then result2
else result3

For example, customer value bands may be defined as:

= Table.AddColumn(
    Source,
    "ValueBand",
    each if [AnnualSales] = null then "Unknown"
         else if [AnnualSales] >= 100000 then "Platinum"
         else if [AnnualSales] >= 50000 then "Gold"
         else if [AnnualSales] >= 10000 then "Silver"
         else "Bronze",
    type text
)

Order matters. The highest threshold must be tested first; otherwise a value of 120,000 would be classified by the first lower threshold it encountered.

A service-level classification could be:

= Table.AddColumn(
    Source,
    "SLAStatus",
    each if [ElapsedHours] = null then "Open or Missing Time"
         else if [ElapsedHours] <= [SLAHours] then "Within SLA"
         else "Breached SLA",
    type text
)

Null handling is not optional. A null may mean unknown, not applicable, not yet completed or invalid. These meanings should not be collapsed without a documented decision.

Multiple Conditions

Power Query supports Boolean operators:

if [Priority] = "High" and [CustomerTier] = "Gold" then "Escalate"
else if [Priority] = "High" or [ElapsedHours] > 24 then "Review"
else "Standard"

Complex nested expressions can become difficult to audit. When rules are numerous or maintained by business users, a rule table is often superior. A rule table can contain minimum and maximum values, categories, effective dates and priority order. The query then joins or selects the applicable rule. This approach separates policy from code and supports controlled change management.

7. Nulls, Errors and Data Quality

Null, blank text, zero and error are different states:

• null means no value is present;
• "" is an empty text value;
• 0 is a numeric value;
• an error means the expression could not produce a value.

Testing should reflect the data type. For example:

if [Status] = null then "Missing"
else if Text.Trim([Status]) = "" then "Blank"
else Text.Trim([Status])

If a column may contain non-text values, convert carefully or use type checks. A reliable process records the number of rejected rows and identifies their sources. Silent coercion can produce apparently complete reports with incorrect classifications.

8. Applied Case: Regional Retailer Service Dashboard

Background

This illustrative case concerns Northstar Retail, a fictional retailer operating in the United Kingdom and the United States. Its service-ticket export contains OpenedText, ClosedText, Region, Priority, CustomerTier and TicketID. Dates are exported as text in regional formats, and management wants monthly ticket volume, fiscal-year performance and SLA compliance.

Problem

The existing report has four weaknesses. UK dates are interpreted as US dates, open tickets produce duration errors, weekend tickets are included in business-hour performance, and customer names differ because of inconsistent spacing and casing.

Analysis

The reporting team identifies the following rules:

1. UK dates use en-GB; US dates use en-US.
2. The fiscal year begins on 1 April and is labelled by its ending year.
3. SLA time is measured in elapsed hours for the first release, with a later business-hours enhancement.
4. Missing close times are classified as open, not as SLA breaches.
5. Customer names are trimmed and cleaned, but master-data matching remains the authoritative identity process.

Approach and Solution

A region-aware date conversion is implemented using a conditional culture:

= Table.AddColumn(
    Source,
    "OpenedAt",
    each try DateTime.FromText(
        [OpenedText],
        if [Region] = "UK" then "en-GB" else "en-US"
    ) otherwise null,
    type datetime
)

The query then adds a data-quality flag for failed conversions, calculates duration only when both timestamps exist, and joins the ticket date to the central calendar. Fiscal attributes are inherited from the calendar rather than recalculated independently in the ticket query.

A classification column is created:

= Table.AddColumn(
    PreviousStep,
    "ManagementClass",
    each if [ClosedAt] = null then "Open"
         else if [ElapsedHours] <= [SLAHours] then "Compliant"
         else "Breached",
    type text
)

Outcome

The revised report distinguishes invalid dates from genuinely open tickets, applies a consistent fiscal calendar and provides a transparent SLA classification. Managers can trace exceptions to the source text rather than treating missing values as satisfactory performance.

Learning Points

The case demonstrates that transformation logic is part of reporting governance. A technically valid query can still be analytically wrong if locale, fiscal definitions, time basis or null semantics are not agreed. Centralised calendar logic and explicit exception handling improve both trust and maintainability.

9. Implementation Practices and Limitations

Query Design

Use descriptive step names such as TypedDates, AddedFiscalYear and ValidDurations. Keep source values when auditability matters, and remove them only after validation. Apply filters and column selection early where this preserves query folding and reduces data volume.

Query Folding

When the source system can perform transformations, Power Query may translate steps back to the source through query folding. Simple type conversions and filters often fold well; custom functions, complex row-by-row text operations and some calendar generation steps may not. Use the query-folding indicators and performance tools where available. Correctness remains more important than folding, but unnecessary non-folding operations can slow refreshes.

Time Zones and Daylight Saving

A fixed offset is not always equivalent to a named time zone. Daylight-saving transitions can create ambiguous or nonexistent local times. For operational systems spanning regions, store UTC timestamps where possible and retain the source time-zone identifier. Convert for presentation only after the reporting requirement is known.

AI-Assisted Development

AI tools can suggest M expressions, but generated code must be tested against locale variations, nulls, boundary dates, daylight-saving changes and business-rule thresholds. An AI-generated classification is not a policy decision. The organisation remains responsible for documenting and approving the rule.

Governance and Ethics

Date and text transformations can affect performance measures, customer segmentation and employee evaluation. Altering time zones or excluding non-working periods may materially change a service metric. Definitions should therefore be approved by accountable business owners, version-controlled and communicated to report users. Sensitive text should not be sent to external AI services without authorisation and appropriate data protection controls.

Best-Practice Checklist

• Confirm the meaning and time zone of every temporal field.
• Convert ambiguous text with an explicit culture.
• Retain original values when auditability is important.
• Build one governed calendar for shared reporting definitions.
• Document fiscal-year labels and week conventions.
• Use Duration.TotalHours for aggregate elapsed-hour measures.
• Define business hours, holidays and pause rules before measuring SLAs.
• Handle nulls and errors explicitly.
• Order conditional thresholds from most specific or highest priority to lowest.
• Prefer rule tables when policies change frequently.
• Create exception outputs for invalid records.
• Test boundary dates, regional formats and daylight-saving transitions.

Chapter Summary

Power Query provides the tools required to transform raw temporal and textual data into reliable reporting attributes. Dates, times, datetimes and datetimezones must be distinguished by meaning, not merely by appearance. Locale-aware conversion prevents regional interpretation errors, while explicit error handling exposes invalid source records.

A governed calendar supports calendar and fiscal analysis, including fiscal-year and fiscal-month logic. Duration calculations require consistent time bases, and business-hour measures require schedules, holidays and pause definitions. Text functions support cleaning, parsing and combining, but automated formatting should not replace authoritative master data. Conditional expressions implement business classifications, while rule tables improve maintainability when policies are numerous or frequently changing. Across all these tasks, documentation, null semantics, testing, governance and auditability are essential to trustworthy managerial reporting.

Key Terms

Temporal data type: A data type representing a date, time or date-time value.

Locale: Regional conventions governing date, time, number and text interpretation.

Culture code: A code such as en-GB or en-US used to control locale-aware conversion.

date: A calendar date without a time component.

datetime: A date and time without an explicit time-zone offset.

datetimezone: A date and time accompanied by a UTC offset.

Duration: The interval between two temporal values.

Elapsed hours: Total time between two timestamps, commonly obtained with Duration.TotalHours.

Calendar table: A governed table containing one row per reporting date and related attributes.

Fiscal year: An accounting year that may begin in a month other than January.

Fiscal month: A month numbered according to the organisation’s fiscal calendar.

Business day: A date treated as operationally available under an agreed schedule.

Locale-aware conversion: Conversion that explicitly applies regional interpretation rules.

Text parsing: Extracting or separating meaningful components from text.

Conditional logic: Rule-based evaluation using if, then and else expressions.

Classification: Assigning records to business categories according to defined rules.

Null: The absence of a value, distinct from zero, blank text or an error.

Query folding: The translation of Power Query steps into operations performed by the source system.

Service-level agreement: A defined performance commitment, including its measurement rules.

Exception report: An output identifying records that fail validation or transformation rules.

Review Questions

Short Answer Questions

1. What is the difference between datetime and datetimezone in Power Query?
2. Why should an explicit culture be supplied when converting ambiguous date text?
3. What is the purpose of a calendar table?
4. Why is Duration.TotalHours generally preferable to Duration.Hours for aggregation?
5. How do null, blank text and error differ?

Long Answer Questions

1. Explain how to design a fiscal calendar for an organisation whose fiscal year begins in April. Include the fiscal-year labelling decision.
2. Discuss the risks of calculating SLA performance from timestamps recorded in different time zones.
3. Explain how text-cleaning and text-parsing operations should be governed in a customer-data pipeline.
4. Compare nested conditional logic with a rule-table approach for business classification.
5. Describe a robust process for converting regional date text while preserving data-quality exceptions.

Analytical / Application Questions

1. A ticket is opened on Friday at 16:00 and closed on Monday at 10:00. Explain why elapsed hours and business hours produce different results, and specify the additional rules needed for a business-hours calculation.
2. A dataset contains 04/05/2025, with records from both the UK and the United States. Design a Power Query approach that interprets each record correctly and identifies failed conversions.
3. A manager claims that all records with missing close times should be counted as SLA breaches. Evaluate this rule and propose a more analytically defensible classification scheme.

Multiple Choice Questions with Answers

Question 1

Which Power Query type includes a date, time and UTC offset?

A. date

B. time

C. datetime

D. datetimezone

Correct Answer: D. datetimezone

Question 2

Which culture is most appropriate for a day-month-year source used in the United Kingdom?

A. en-GB

B. en-US

C. ja-JP

D. de-DE

Correct Answer: A. en-GB

Question 3

What does Duration.TotalHours return?

A. Only the hour component of a duration

B. The complete duration expressed as hours

C. The number of calendar dates in a duration

D. The time-zone offset

Correct Answer: B. The complete duration expressed as hours

Question 4

For an April-start fiscal year labelled by its ending year, which fiscal year contains 15 April 2025?

A. FY2024

B. FY2025

C. FY2026

D. FY2027

Correct Answer: C. FY2026

Question 5

Which function removes leading and trailing spaces from text?

A. Text.Trim

B. Text.Middle

C. Text.Combine

D. Text.End

Correct Answer: A. Text.Trim

Question 6

Why should higher classification thresholds generally be tested before lower thresholds?

A. To reduce the number of columns

B. To prevent high values being captured by a lower category

C. To force all nulls to zero

D. To enable query folding

Correct Answer: B. To prevent high values being captured by a lower category

Question 7

What is the most appropriate treatment when a close timestamp is missing for an open ticket?

A. Automatically classify it as compliant

B. Automatically classify it as breached

C. Classify it as open or missing time and exclude it from completed-ticket SLA results

D. Replace the timestamp with the refresh date without documentation

Correct Answer: C. Classify it as open or missing time and exclude it from completed-ticket SLA results

Question 8

Which approach is usually most suitable when business classification rules change frequently and are maintained by business users?

A. Hard-code all rules in nested expressions

B. Use a rule table with documented priorities and thresholds

C. Delete records that do not match a rule

D. Convert categories to random text labels

Correct Answer: B. Use a rule table with documented priorities and thresholds

Question 9

What does query folding mean?

A. Compressing text values into shorter strings

B. Moving all data into a calendar table

C. Translating Power Query operations into source-system operations

D. Changing a datetime into a date

Correct Answer: C. Translating Power Query operations into source-system operations

Question 10

Which statement about Text.Proper is most accurate?

A. It is always a reliable substitute for master-data matching

B. It converts every value into a numeric identifier

C. It can improve presentation but may damage legitimate names or brand styling

D. It removes all invalid dates

Correct Answer: C. It can improve presentation but may damage legitimate names or brand styling