Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Isblank function and concatenate in Direct Query

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?

Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi Anonymous,

     

    try the following formula:

     

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

    Regards,

    MFelix

     

     

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi Anonymous,

     

    try the following formula:

     

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

    Regards,

    MFelix

     

     

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.