Creating custom functions in Power Query using the M language allows you to reuse logic and streamline your data transformation processes. Here’s a step-by-step guide on how to create and use custom functions in Power Query:

Steps to Create Custom Functions

1. Open Power Query Editor

1. In Excel:
- Go to the `Data` tab and select `Get Data` > `Launch Power Query Editor`.

2. Create a New Blank Query

1. In Power Query Editor:
- Go to the `Home` tab and select `New Source` > `Blank Query`.
- In the Queries pane, right-click on the new query and select `Rename` to give it a meaningful name (e.g., `MyCustomFunction`).

3. Open the Advanced Editor

1. In Power Query Editor:
- With the new blank query selected, go to the `Home` tab and select `Advanced Editor`.

4. Define Your Custom Function

1. In the Advanced Editor:
- Write the M code for your custom function. The syntax for defining a function in M is:
```m
(parameter1 as type1, parameter2 as type2, ...) =>
let
// Intermediate calculations or transformations
in
// Final result
```

Example: Custom Function to Calculate Discounted Price

Let’s create a simple custom function to calculate the discounted price given the original price and discount rate.

1. Function Definition:
```m
(originalPrice as number, discountRate as number) as number =>
let
discountedPrice = originalPrice * (1 - discountRate)
in
discountedPrice
```

2. Enter the Function Code:
- Copy and paste the function code into the Advanced Editor and click `Done`.

5. Use the Custom Function in a Query

1. Load Your Data:
- Go to the `Home` tab and select `New Source` > `From File` (e.g., `From Workbook`) to load your data into Power Query.

2. Invoke Custom Function:
- In the Queries pane, right-click on your data query and select `Edit` to open it in the Power Query Editor.
- Add a new custom column by going to the `Add Column` tab and selecting `Custom Column`.
- In the `Custom Column` dialog box, enter a column name (e.g., `DiscountedPrice`) and use the following formula to invoke the custom function:
```m
MyCustomFunction([OriginalPrice], [DiscountRate])
```
- Click `OK` to add the custom column.

Example: Using the Custom Function

Assume you have a table with columns `OriginalPrice` and `DiscountRate`.

1. Add Custom Column:
- In the `Add Column` tab, select `Custom Column`.
- In the `Custom Column` dialog, use the custom function:
```m
= MyCustomFunction([OriginalPrice], [DiscountRate])
```
- Click `OK`.

2. Result:
- The new column `DiscountedPrice` will be added to your table with the calculated discounted prices.

Tips for Creating Custom Functions

- Parameter Types: Always specify parameter types for better performance and error handling.
- Error Handling: Add error handling logic within your custom function to manage unexpected inputs or errors.
- Documentation: Comment your function code to explain the logic and make it easier for others (or yourself) to understand later.
- Reusability: Store commonly used custom functions in a separate query group for easy access and reuse across different queries.

Advanced Example: Function with Multiple Parameters and Conditional Logic

Let's create a more advanced custom function that calculates shipping costs based on weight and destination.

1. Function Definition:
```m
(weight as number, destination as text) as number =>
let
baseCost = 5.0,
weightFactor = if weight <= 10 then 0.5 else 1.0,
destinationFactor = if destination = "Domestic" then 1.0 else 2.0,
shippingCost = baseCost + (weight * weightFactor * destinationFactor)
in
shippingCost
```

2. Enter the Function Code:
- Copy and paste the function code into the Advanced Editor of a new blank query and click `Done`.

3. Use the Custom Function:
- In your data query, add a custom column and invoke the function:
```m
= MyShippingCostFunction([Weight], [Destination])
```

By following these steps, you can create and use custom functions in Power Query to enhance your data transformation workflows and make them more efficient.