I have been reading and helping out where I can on the Power BI Community forums. And what I often see is people trying to use the Calculated Columns in the Power BI Desktop Model.

So in this blog post below I am going to explain why it is better, quicker and more efficient to use the Query Editor to create additional columns.

Example Scenario

For this example, what I am going to be doing is searching within the Description
column for a piece of text, and then creating a new column which specifies the area.

The two pieces of text that I want to find are “Gold Coast” and “Brisbane“, which will then go into my new column called Area.

If I cannot find “Gold Coast” or “Brisbane” I will then make it “Unknown”

The harder way doing it with a Calculated Column

Below are the steps where you can do this using a calculated column in your Power BI Model.

  • To do this I opened up my Power BI Desktop file and then in the columns clicked on New Column
  • Now in order to find the piece of text that I am looking for I have to use the SEARCH
    function in DAX, but as you can see below in order for this to work I have to add in some additional DAX functions

    Area (Model) =

     IF (

        IFERROR ( SEARCH ( “*Gold Coast*”, [Description] ), -1 ) = 1,

        “Gold Coast”,

    IF (

    IFERROR ( SEARCH ( “*Brisbane*”, [Description] ), -1 ) = 1,

    “Brisbane”,

    “Unknown”

    )

    )

  • As you can see above I have had to use the IFERROR, so that if there is indeed an error I can capture this, and not cause the calculated column to fail.
  • As well as the outer IF condition is where I am saying if the SEARCH = 1 meaning that it is TRUE or Correct, then what Text I want it to display.
  • And as with my two conditions this is for the “Gold Coast” and “Brisbane”
  • And then finally if either of the conditions are not met, then make the output “Unknown“
  • NOTE: There might be a more efficient way to do this, and if there is please leave it in the comments section.

As you can see with the above method, this is very clunky as well as very prone to errors.

As well as in the past when I had quite a few conditions I was searching for, it becomes very long and complex.

The easier way, using the Query Editor

Below I am going to show how to achieve the same result in a much easier way using the Query Editor.

  • To do this I opened up my Power BI Desktop file and clicked on Edit Queries
  • Now once the Query Editor Opened I clicked on Add Column in the Ribbon.
  • Next I selected Conditional Column and create it as shown below.
  • As you can see above this is searching for my requirements within the Description column.
  • I then clicked Ok, and then clicked Close And Apply to Load the data into my Power BI Desktop Model.

As you can see to create the conditional column is very easy and simple to search for text and create the additional column.

As well as if there is a requirement for additional conditions, it is very easy to add.

You can see below that both columns have the same output.

Conclusion

So in conclusion you can see that there is a simple way, as well as a more complex way to get the same desired output.

I would also like to note that with my current understanding of Power BI and how the compression and performance works, that it is best practice to ideally create your columns in your source data. If you cannot it is then next best to do it within the Query Editor. And then last resort is in the Power BI Model.

And with each process above being less optimal.

And in my opinion I do not see why you would need to create a Calculated Column, when this can very easily be achieved in the Query Editor.

You can download the sample file here: Calculated Column.pbix