Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ShadowyPrecepts
New Member

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

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?

1 ACCEPTED SOLUTION

Hi, @ShadowyPrecepts 

 

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:

vjaneygmsft_0-1649062711217.png

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

View solution in original post

3 REPLIES 3
ShadowyPrecepts
New Member

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?

Hi, @ShadowyPrecepts 

 

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:

vjaneygmsft_0-1649062711217.png

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
Super User
Super User

@ShadowyPrecepts , A new column in DAX

if(isblank([Column A])  ,

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.