← Back to Chapters

DateTime, Text & Logical Formulas

? Power Query Editor – DateTime, Text & Logical Formulas

✨ Quick Overview

DateTime, Text, and Logical formulas are used in Power Query (M Language) to transform data during the ingestion phase. This ensures that only clean, categorized data enters the data model, making your Power BI reports faster.

? Key Concepts

  • Add Column: Most custom formulas are entered via "Custom Column" in the Add Column tab.
  • M-Language: Power Query's internal language; it is strictly case-sensitive.
  • ETL Efficiency: Performing these calculations in Power Query is usually better for performance than using DAX Calculated Columns.

? Explanation: Date & Time, Text, and Logical Formulas

1. Date & Time Formulas

Used for time-stamping and calculating durations.

// Returns current date and time DateTime.LocalNow() // Extracts only the date part DateTime.Date([OrderTimestamp])

2. Text Formulas

Used for cleaning and formatting string data.

// Standardize to Uppercase Text.Upper([Status]) // Combine text columns [FirstName] & " " & [LastName]

3. Logical Formulas

Used for branching logic and categorization.

// Basic if-then-else logic if [Amount] > 1000 then "High" else "Low"

? Use Cases

  • DateTime: Creating a "Refresh Date" column to show when data was last updated.
  • Text: Cleaning messy user inputs (trimming spaces or fixing casing) for consistent grouping.
  • Logical: Creating custom Age Buckets (e.g., 0-30, 31-60) from a "Days Overdue" column.

? Interactive Formula Simulator

Transform the sample data: {Status: "pending", Amount: 5000}

Output Preview: Select an action...

? Best Practices

  • Syntax Check: Always use Proper.Case for M functions (e.g., Text.Trim, not text.trim).
  • Data Typing: Always click the data type icon (ABC/123) in the column header after creating a custom column.
  • Avoid Nulls: When writing logical formulas, always consider how to handle null values to avoid errors.

? Try It Yourself

Step-by-Step Task:
  1. Navigate to Add Column → Custom Column.
  2. Type DateTime.LocalNow() to see the current system time.
  3. Try if [Sales] > 500 then "Pass" else "Fail".
  4. Use Text.Length([ProductCode]) to find the character count of a string.