Isblank function and concatenate in Direct Query

[Anonymous]

I wanna create a column "Bay & Level" using PickAisle, PickModule, PickBay & PickLevel.

Eg: 42B-003-3. If the value is NULL, I want it to be blank. 

Mode: Direct Query

I am getting the values correctly except for NULL values. Could someone help me?

[Anonymous]

Not sure if your expectation is the following result. If it is, you can try use Power Query Column from Examples feature to do it.

Go to Query Editor->Column from Examples->From All Columns, fill in the merged results in the first value, then Power BI will create the following values accordingly.

[MFelix · Accepted solution]

Hi @Anonymous,

try the following formula:

[PickAisle] & "-" &(if [PickModule] = null then " " else [PickModule]) & "-" & [PickBay] & "-" & [PickLevel]

Regards,

MFelix

[workonpower]

Hi, can I see the whole formula for this please?

[MFelix]

Hi @workonpower ,

Not sure if this is what you are refering to but:

[PickAisle] & "-" & (
  if [PickModule] = null then " "
  else [PickModule]) & "-" & [PickBay] & "-" & [PickLevel]

This formula is for M language used in the query editor.

Regards,

MFelix