Removing data while in power query

[Vegas01]

Hi

This is probably a really simple question but have not been able to work it out.

In the last column I would like to delete all of the data that does not start with "L" so they become a null so I have fill down with the L data.

Previously I have been taking the spreadsheet back to excel deleting the data, then bring back to Power query to do the Fill Down function. I would like to try and do it all in the one place.

It is doable?

 

[alansidman]

Click on Add Column-->Custom Column
type

=Text.StartsWith([Vendor No],"L") then [Vendor No] else null

Change the name of the new column from Custom to Vendor Nr
Highlight column Vendor No and right click and select Remove.

 

[Vegas01]

Alansidman

Thanks for that but it is giving me an error on the "Then"

I understand what needs to be done but not sure why the error. Thanks in advance.

 

[alansidman]

My bad, I forgot to place an if in front of the statement.. It should read

if Text.StartsWith(Vendor No), "L") then [Vendor No] else null

 

[Vegas01]

Alansidman

Thanks for that I got it to work.

But now it was given me the error in the cells, which will not allow the Fill down of the vendor number to work.

So some how have to remove the errors and replace them with null

 

[Vegas01]

Alansidman

Thanks for all of your help, I solve the error issue myself. I will learn ow this works.