Use M-Query to add a column to your data

If you want to add an extra column to your table, then you can do so by creating a Custom Column in Power BI desktop. This option allows you to perform various actions such as creating a copy of another column. Thereafter modifying the copied column by making use of an If-statement or adding in some calculations.

In this blog, I will take you through an example of adding a Custom Column that includes an If statement and a Sum.

(Step 1) Select Custom Column from the Query Editor

Navigate to the Add Column option, then click on Custom Column:

(Step 2) Give your new column a name

From the name box, you can type in the name of your column.

(Step 3) Create a formula for the column 

For this example, I selected the “Solved Date/mitigation strategy date…” column as well as the “Correct Date of identification column”.

These columns are selected from the Available columns option:

In the final formula, I am trying to calculate the duration of days between two periods. The If-statement is included for cases where the date field from my table was left blank. In those cases, it will use the date from today. However, the main point of this blog is not to go into too much detail about the formula, but to show how easy it is to build various formulas for a custom column in Power Query.

(Step 4) Check that there are no syntax errors in your formula then press OK

If your formula is not correct, you will see the following error message:

Once you’ve corrected the formula the message will change to the following:

Once you’ve pressed OK, you will see that a new applied step has been added in your Query Settings:

(Step 5) Use the Advanced Editor to make changes to the Custom Column

From the Advanced Editor, you can easily amend your custom column and even add more columns if you want.

Simply navigate to Advanced Editor tab from the Query editor view:

And once you click on it, you should see the details pertaining to the column that you’ve created:

In Summary

From the above example, you can see how easy a custom column can be created in the Query editor. You can even go straight to the advanced editor to ad data column to your table.

For other interesting blogs, you can also visit our blog site at: https://databear.com/blog/

Official Microsoft information pertaining to this topic: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-add-custom-column

Enrol for training:  https://databear.com/power-bi-training/