In Power Query, you can perform various date and time manipulation functions to transform, extract, and manipulate date and time values within your data. These functions are essential for tasks such as deriving new date attributes, calculating intervals, or formatting dates in specific ways. Here’s how you can perform date and time manipulation in Power Query:

Date and Time Functions in Power Query

1. Extracting Date and Time Components

- Date Functions:
- Date.Year: Extracts the year from a date.
```m
Date.Year(DateColumn)
```
- Date.Month: Extracts the month from a date (as a number).
```m
Date.Month(DateColumn)
```
- Date.Day: Extracts the day of the month from a date.
```m
Date.Day(DateColumn)
```

- Time Functions:
- Time.Hour: Extracts the hour from a time value.
```m
Time.Hour(TimeColumn)
```
- Time.Minute: Extracts the minute from a time value.
```m
Time.Minute(TimeColumn)
```
- Time.Second: Extracts the second from a time value.
```m
Time.Second(TimeColumn)
```

2. Date Arithmetic and Calculations

- Date.AddYears: Adds or subtracts years from a date.
```m
Date.AddYears(DateColumn, 1) // Adds 1 year to DateColumn
```

- Date.AddMonths: Adds or subtracts months from a date.
```m
Date.AddMonths(DateColumn, 3) // Adds 3 months to DateColumn
```

- Date.AddDays: Adds or subtracts days from a date.
```m
Date.AddDays(DateColumn, -7) // Subtracts 7 days from DateColumn
```

3. Formatting Dates and Times

- Date.ToText: Converts a date value to text with a specified format.
```m
Date.ToText(DateColumn, "yyyy-MM-dd") // Formats date as "YYYY-MM-DD"
```

- Time.ToText: Converts a time value to text with a specified format.
```m
Time.ToText(TimeColumn, "HH:mm:ss") // Formats time as "HH:MM:SS"
```

4. Handling Date-Time Zones

- DateTimeZone.SwitchZone: Converts date-time values between different time zones.
```m
DateTimeZone.SwitchZone(DateTimeColumn, -5) // Converts to UTC-5 timezone
```

5. Working with Date-Time Differences

- Duration.Days: Calculates the difference in days between two date-time values.
```m
Duration.Days(DateTimeColumn1 - DateTimeColumn2)
```

Example: Using Date and Time Functions

Suppose you have a dataset `SalesData` with a `OrderDate` column that contains date values. To extract year, month, and day, and format the date:

1. Open Power Query Editor:
- Select `Home` > `Transform Data` to open the Power Query Editor.

2. Date and Time Transformations:

- Extract Year, Month, Day:
```m
#"Extracted Year" = Date.Year(SalesData[OrderDate]),
#"Extracted Month" = Date.Month(SalesData[OrderDate]),
#"Extracted Day" = Date.Day(SalesData[OrderDate])
```

- Format Date:
```m
#"Formatted Date" = Date.ToText(SalesData[OrderDate], "yyyy-MM-dd")
```

- Add Days to Date:
```m
#"Date Plus 7 Days" = Date.AddDays(SalesData[OrderDate], 7)
```

3. Load Data:
- After applying the transformations, click `Close & Load` to load the modified data back into Power BI or Excel.

Additional Tips:

- Error Handling: Ensure date and time values are formatted correctly in your dataset to avoid errors when applying date and time functions.
- Using Parameters: Use parameters in Power Query to dynamically control date ranges or other date-related filters.
- Data Types: Pay attention to data types (Date/Time, Text) when performing operations to maintain consistency and avoid unexpected results.

By leveraging these date and time manipulation functions in Power Query, you can effectively transform and derive insights from date-related data within your datasets, enhancing your analytical capabilities in Power BI or Excel.