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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

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, @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:

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
Anonymous
Not applicable

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, @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:

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

@Anonymous , A new column in DAX

if(isblank([Column A])  ,

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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