There are two main ways to create custom columns with formulas in Power Query (M Language):

1. Using the Custom Column Dialog:

This is the easiest method for beginners. Here's how to do it:

1. Select the "Add Column" tab: In the Power Query Editor ribbon.
2. Choose "Custom Column": Click on the "Custom Column" option.
3. Define the new column: A dialog box will appear. In the "New column name" field, enter a descriptive name for your new column.
4. Write your formula: In the "Custom column formula" box, type your M formula to define the calculation for the new column. You can reference existing columns using their names and operators like `+`, `-`, `*`, and `/`. Power Query also offers a variety of functions for data manipulation and transformation.

Here's an example:

Suppose you have a "Sales" column and a "Discount" column (percentages), and you want to create a new column named "Sales after Discount" that calculates the discounted sales amount. You could use the following formula:

```
Sales after Discount = Sales * (1 - Discount)
```

5. Set the data type (optional): You can specify the expected data type for your new column (e.g., Decimal, Text). If not specified, Power Query will try to infer the type based on the formula.
6. Click "OK": Once you're satisfied with your formula, click "OK" to create the new custom column in your table.

2. Directly editing the query code:

For more advanced users or complex formulas, you can edit the query code directly. Here's the basic structure:

```
let
Source = ..., // Your original data source
CustomColumnName = Table.AddColumn(Source, "CustomColumnName", CustomColumnFormula),
in
CustomColumnName
```

- Replace "..." with your existing data source steps.
- "CustomColumnName" is the name you want to give to your new column.
- "CustomColumnFormula" is the M formula defining the calculation for the new column.

This method gives you more flexibility to integrate your custom column creation within the overall data transformation process.

Tips:

* Use the formula bar for auto-completion and syntax help while writing your M formula.
* Explore the built-in Power Query functions for various data manipulation tasks.
* Test your formulas on a small sample of data before applying them to your entire dataset.


By following these steps and understanding the concepts, you can effectively create custom columns with formulas in Power Query (M Language) to enrich your data analysis.