Using Variables While Creating Custom Columns in Power Query

Problem Statement:

How can we use variables effectively while creating custom columns in Power Query?

Solution

In Power Query, variables can be declared using the let and in keywords, making calculations more readable and manageable.

Scenario 1: Basic Calculation Using Variables

In the below example, in a custom column simple calculation is performed where the addition of three numbers is shown using variables.

M code is as follows:

 

 

 

Scenario 2: Conditional Calculation Using Variables

In the below example, in the Sales table there are two columns which are Unit Price and Quantity. In a single calculated column, sales value is calculated and whether the sale is above 2000 or below is mentioned using variables.

 

M Code Example:

 

 

Conclusion

Using variables in Power Query enhances readability, simplifies complex calculations, and reduces redundancy. You can create multiple variables within a single custom column to compute various calculations efficiently.

 

Best Regards

Anmol Malviya
Data Analyst | Addend Analytics