Custom Column Power Query

[Quiquegl]

I want to create a custom column that returns me the value of another column cell if the text contains a specific word.

If the text doesn't contain the word I am looking for, then return null.

What would be the IF statement in Power Query?

Example:
Column Name: Office
Account: 640.000011 Expatiate
Office: BR Brussels
Department: AD
Office: SU Seoul

Desired Result: Insert a new column that shows only rows values like "Office: BR Brussels" and Office: SU Seoul, the rest of column values are nulls.
.

 

[FranzV]

You can do this using the 'Conditional Column' button in the 'Add Column' tab of Power Query. A window will let you select what conditions are to be met and what are the desired results if the condition is met or otherwise.

The code would be:

    Table.AddColumn( 
        Source, 
        "Custom", 
        each 
            if Text.StartsWith([Columna1], "Office:") 
            then [Columna1] 
            else null 
    )

To fo from the blue table to the green.

Excel 2016 (Windows) 64 bit

Columna1		Columna1	Custom
Example:		Example:
Column Name: Office		Column Name: Office
Account: 640.000011 Expatiate		Account: 640.000011 Expatiate
Office: BR Brussels		Office: BR Brussels	Office: BR Brussels
Department: AD		Department: AD
Office: SU Seoul		Office: SU Seoul	Office: SU Seoul

<tbody>
</tbody>

 

[Quiquegl]

Thank you FranzV. I don't see 'Conditional Column' button, probably because I am using Excel 2010 at my firm. I will test it from home.

 

[FranzV]

It was added in June 2016, but you can just use the code in previous versions.

 

[Quiquegl]

It was added in June 2016, but you can just use the code in previous versions.

I used the 'Conditional Column' button in Excel 2016 and it works wonderfully without entering any code. What a huge improvement!!

Thank you!