Filling blank values based on a string in another column

[vcb_001]

Hi everyone,

I have two columns in my data table, and I need to transform null values based on a string in the previous column. The structure of the data is quite straigthforward but, the issue is having to look all characters in the string of the specific cell if the main column's value is blank.

FirstColumn	SecondColumn
CHICAGO-SLS-1001	Unknown
TEXAS-SLS-3003	sales
DENVER-SCM-203123	supply chain
CHICAGO-SLSS-2300	Unknown
HOUSTON-SCM-2021	Unknown

What I would like to do is if the data on the first column contains 'SLS' or 'SLSS' anywhere, I would like to fill only the 'Unknown' value as 'sales' on the second column. However, I shouldn't transform the ones like the second row since the data has been already there by default. Only for the unknown ones, I should get the information from the string in the first column somehow. How can I do that in Power BI?

I'm looking forward to having your feedback.

Thanks a lot for your consideration and support in advance.

[HarishKM]

@vcb_001 Hello,
You can go to power query and select column where you wanted replace value and
Then right click and select replace value.
this pop up box will apear then you can fill values and hit okay to replace unknown with desired result.

Thanks
Harish M

[DemoFour · Accepted solution]

@vcb_001 

Try these steps

  #"Added Conditional Column" = Table.AddColumn(Source, "New Collumn", each if [SecondColumn] = "Unkown" then "Sales" else [SecondColumn]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"FirstColumn", "New Collumn"})
in
    #"Removed Other Columns"

[DemoFour]

Hi @vcb_001 

You can add a conditional column in the Query Editor for this table.  If you need some help to do this I can post you in the right direction. 

Add a conditional column - Power Query | Microsoft Learn

[vcb_001]

I don't want to create a new column, I need to replace values.

[DemoFour]

Hi @vcb_001 

As you stated that you want to replace the null based on another column, then you will need some logic to do this.

You can add the logic to bring through the column you are using into the new column with the replaced values as well, then remove that column, so you are left with the one column and the values you need. 

This is a simple transformation in PQ and will evaluete the table at source and push trhough the desired output to use in your model.