Replacing Blank with word "Balnk" ?

[dineshkumar_vrv]

Hi, I want to replace my blanks into word "Blanks" so that I can show them in the slicer as blanks itself as one line item, is it possible ??

I have list of word in the 1st column 

column1

Apple

Bag

Car

             <- Blank 

Echo 

Expecting Output:

column 

Apple 

Bag 

Car 

(Blank)

Echo 

Thank you in advance 

[MFelix · Accepted solution]

Hi @dineshkumar_vrv,

In the advance query choose the replace values "" to  "blank"

Regards

MFelix

[Anonymous · Accepted solution]

Hi

  You can use the replace value transform in Query editor.

Don't enter any value on "Value To Find". We can achieve the same result using below methods,

1. Custom Column with IF condition

2. New column using DAX expression - IF

Thanks

Hari

[Anonymous]

Hello everyone,

I am pretty much not sure if the solution is old.

Today when I was trying to replace spaces with NULL using the "Replace values" option it wasn't allowing me to leave the text box with blank. The column was a date field then by writing in M code rather than UI option it worked.

Thanks,

Karthik

[MFelix]

Hi @Anonymous,

This is possible in M has you refer but in this specific case the user wanted the solution in DAX.

But good insight. 

[MFelix · Accepted solution]

Hi @dineshkumar_vrv,

In the advance query choose the replace values "" to  "blank"

Regards

MFelix

[dineshkumar_vrv]

Thank you

[dineshkumar_vrv]

hi

Are you telling the advanced editor ??

[Anonymous · Accepted solution]

Hi

  You can use the replace value transform in Query editor.

Don't enter any value on "Value To Find". We can achieve the same result using below methods,

1. Custom Column with IF condition

2. New column using DAX expression - IF

Thanks

Hari

[dineshkumar_vrv]

Hi Hari,

Thanks it worked. I have one more query kindly suggested a solution on below.

I used below DAX to concate and I got ouput where it giving extra coma if the value in the one column is  null. 

New column =dataset_name[level1] &","& dataset_name[Level2] &","& dataset_name[Level3]

How to get the desired output which i have highlighted in RED.

Thank you in Advance

[Anonymous]

Hi

  you can use the "Substitute" function in DAX on top of your final column.

 SUBSTITUTE(TableName[ColumnName],",,",",")

Thanks

Hari

[dineshkumar_vrv]

Hi Hari,

That idea works partially because I am getting the comma before, after and in between the word(refer below image) now if i use the substitute function I may loose comma in between words, but i want to remove the commas before and after the words only. inbetween thw words it should be present. 

 

[MFelix]

HI @dineshkumar_vrv,

I have made this test in my PBI see if it works for what you want.

Column =
VAR level1 =
    SWITCH (
        TRUE (),
        Dataset_name[Level1] = BLANK (), BLANK (),
        Dataset_name[Level1] & ","
    )
VAR level2 =
    SWITCH (
        TRUE (),
        Dataset_name[Level2] = BLANK (), BLANK (),
        Dataset_name[Level2] & ","
    )
VAR level3 =
    SWITCH (
        TRUE (),
        Dataset_name[Level3] = BLANK (), BLANK (),
        Dataset_name[Level3]
    )
RETURN
    IF (
        RIGHT ( level1 & level2 & level3 )
            = ",",
        LEFT ( level1 & level2 & level3, LEN ( level1 & level2 & level3 ) - 1 ),
        level1 & level2
            & level3
    )

Regards,

MFelix

[dineshkumar_vrv]

Hi MFelix,

The above lines of code is not working It dosent give the comma in between words.

[MFelix]

Can you post your measure?

MFelix

[dineshkumar_vrv]

Hi MFelix,

I will rewrite my problem statement, please find the below screen shot.

Points:

1.I have concatenated the columns L1,L2,L3,L4,L5 -> output (Refere above table)

2.Next I used Substitute function and replaced "," ",," ",,," ",,,," all these sets of comma into single "," -> Substitute (Refere above table)

3.Now please suggested how to get the Desired output removing the unwanted commas in fornt of the words and end of the words but to keep the comma in between concatenated words ?

[MFelix]

Are you doing this in DAX (PBI) or in M (Query Editor)?

MFelix

[dineshkumar_vrv]

DAX in power Bi

[MFelix · Accepted solution]

HI @dineshkumar_vrv,

I have remade my formula to adjust to your values please add this as a column to your table

Column =
VAR L1 =
    SWITCH (
        TRUE (),
        Dataset_name[L1] = BLANK (), BLANK (),
        Dataset_name[L1] & ","
    )
VAR L2 =
    SWITCH (
        TRUE (),
        Dataset_name[L2] = BLANK (), BLANK (),
        Dataset_name[L2] & ","
    )
VAR L3 =
    SWITCH (
        TRUE (),
        Dataset_name[L3] = BLANK (), BLANK (),
        Dataset_name[L3]
    )
VAR L4 =
    SWITCH (
        TRUE (),
        Dataset_name[L4] = BLANK (), BLANK (),
        Dataset_name[L4]
    )
VAR L5 =
    SWITCH (
        TRUE (),
        Dataset_name[L5] = BLANK (), BLANK (),
        Dataset_name[L5]
    )
RETURN
    IF (
        RIGHT ( L1 & L2 & L3 & L4 & L5 )
            = ",",
        LEFT ( L1 & L2 & L3 & L4 & L5 , LEN ( L1 & L2 & L3 & L4 & L5 ) - 1 ),
       L1 & L2 & L3 & L4 & L5 
    )

I check if the value is blank however if the value in each colum is the comma (as appears in your table) replace by the following formula.

Column =
VAR L1 =
    SWITCH (
        TRUE (),
        Dataset_name[L1] = ",", BLANK (),
        Dataset_name[L1] & ","
    )
VAR L2 =
    SWITCH (
        TRUE (),
        Dataset_name[L2] = ",", BLANK (),
        Dataset_name[L2] & ","
    )
VAR L3 =
    SWITCH (
        TRUE (),
        Dataset_name[L3] = ",", BLANK (),
        Dataset_name[L3]
    )
VAR L4 =
    SWITCH (
        TRUE (),
        Dataset_name[L4] = ",", BLANK (),
        Dataset_name[L4]
    )
VAR L5 =
    SWITCH (
        TRUE (),
        Dataset_name[L5] = ",", BLANK (),
        Dataset_name[L5]
    )
RETURN
    IF (
        RIGHT ( L1 & L2 & L3 & L4 & L5 )
            = ",",
        LEFT ( L1 & L2 & L3 & L4 & L5 , LEN ( L1 & L2 & L3 & L4 & L5 ) - 1 ),
       L1 & L2 & L3 & L4 & L5 
    )

Regards,

MFelix

[dineshkumar_vrv]

Hi MFelix,

Thank you so much It really worked and helpled me alot, Special thanks for your patients and sharing your valuable time.

[dineshkumar_vrv]

MFelix,

Thanks I will try and revert back to you.