Creating calculated columns based on existing data in Power Query allows you to derive new values or perform calculations using M language functions. Here’s how you can create calculated columns step-by-step in Power Query:

Steps to Create Calculated Columns

1. Launch Power BI Desktop or Excel:
- Start a new query: `Home` > `Get Data` > `More...` > `Blank Query` or select a data source to begin.

2. Open Power Query Editor:
- Go to `Home` > `Transform Data` to open Power Query Editor.

3. Navigate to the Table:
- Locate and select the table or query from which you want to create a calculated column.

4. Add a Custom Column:

- Method 1: Using Add Column Menu:
- Select the column header or click on a column in the Power Query Editor.
- Go to `Add Column` > `Custom Column`.

- Method 2: Using the Formula Bar:
- Click on `Add Custom Column` in the Formula Bar at the top of the Power Query Editor.

5. Define the Calculation:
- In the `Custom Column` dialog or the Formula Bar, provide a name for your new column.
- Enter the formula using M language syntax to define the calculation based on existing columns.

Example of Creating a Calculated Column

Suppose you have a dataset `SalesData` with columns `UnitPrice` and `Quantity`. To create a calculated column `TotalSales`:

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

2. Add Custom Column:
- Click `Add Custom Column` in the `Add Column` tab or in the Formula Bar.

3. Enter Formula:
- Name the new column as `TotalSales`.
- Enter the formula to multiply `UnitPrice` by `Quantity`:
```m
= [UnitPrice] * [Quantity]
```
- Ensure the formula syntax aligns with M language conventions, using square brackets `[ ]` to reference column names.

4. Click OK:
- Click `OK` to apply the custom column creation.

5. Load Data:
- After creating the calculated column, click `Close & Load` to load the transformed data back into Power BI or Excel.

Additional Tips

- Referencing Columns: Use column names within square brackets `[ ]` to reference existing columns in your calculations.
- Functions: Utilize M language functions such as arithmetic operators (`+`, `-`, `*`, `/`), text functions (`Text.Trim`, `Text.Start`, etc.), and conditional functions (`if...then...else`) as needed.
- Error Handling: Implement error handling functions (`try...otherwise`) to manage potential errors in your calculations.
- Data Types: Ensure that the data types of calculated columns are appropriate for their intended use (e.g., numeric, text, date).

By following these steps and leveraging M language capabilities in Power Query, you can effectively create calculated columns to derive new insights and perform complex calculations based on existing data in your datasets.