Replace empty cells in column A based on substring in column B

[Anonymous]

I have a rather large dataset where I must fill in empty cells in one column based on another. I am using power query, and while I know my way around python and R, Power BI is brand new to me.

To simplify lets say column A (with empty cells) contains marital status. column B contains names, and for reasons (tm) all married individuals have suffix "yay".
For each empty cell in A I must therefore check for the "yay" suffix in B, if present I must change A to "Married" and if absent I must set it to "Single".
A simple if then query I suspect. 
How can I do this?

[Anonymous]

Thank you, @amitchandak . can it be done in M? I would like to do this for the table in the query editor before I start visualisation. I also really do not want a new column. I assume it is supposed to read
if(right([Column B], 3) = "yay"
not
if(right([Column B], 3), "yay"
is that correct?

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

I create a simple sample. You can use text.start and text.end function in power query and create a custom column.

You can try:

if [ColumnA] =""
then 
if Text.End([ColumnB],3)="yay"
then "Married"
else "Single"

else [ColumnA]

Like this:

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[amitchandak]

@Anonymous , A new column in DAX

if(isblank([Column A])  ,

    if(right([Column B], 3), "yay", "married", "Single") , [Column A] )