Forum Discussion
Filling blank values based on a string in another column
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.
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"
5 Replies
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 LearnI don't want to create a new column, I need to replace values.
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.
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"
