How to identify and replace a specific string?

[Netrelemo]

I want to replace all occurences of a particular string in a single column. 

The string must contain a "." in the second position. If it doesn;t or if it is null, I want to put in a "unknown" 

= Table.ReplaceValue(#"Sorted Rows", each not Text.Contains ([itemID],"."),"unknown",Replacer.ReplaceText,{"itemID"})

EXAMPLE

"X.NNNNNNNNNNNNNNN" is legitimate

"123456789" is not and should become "Unknown"

Attempts

= Table.TransformColumns(#"Changed Type", {{"Text", each if Text.ToList(_){1} = "." then _ else "Unknown", type text}})
Expression.Error: The column 'Text' of the table wasn't found.

=if(isnumber(mid(Data[ItemID],2,1)),"legitimate","Unknown")
Expression.SyntaxError: Token RightParen expected.

You can use https://docs.microsoft.com/en-us/powerquery-m/text-functions
Sure.... and when that doesn't work I come to this place

Column = if(mid('Table'[Column1],2,1)=".","legitimate","Unknown")

Expression.SyntaxError: Token Literal expected.

But this seems to have solved the problem: 

= Table.AddColumn(x, "Custom", each if Text.At([ColumnName], 1) = "." then "Legitimate" else "Not")

[ryan_mayu]

@Netrelemo 

please try to create a column

Column = 

if(mid('Table'[Column1],2,1)=".","legitimate","Unknown")

[amitchandak]

@Netrelemo ,

You can use https://docs.microsoft.com/en-us/powerquery-m/text-functions

https://docs.microsoft.com/en-us/powerquery-m/text-at

[Ashish_Mathur]

Hi,

Try this calculated column formula

=if(isnumber(mid(Data[ItemID],2,1)),"legitimate","Unknown")

Hope this helps.

[mahoneypat · Accepted solution]

Here is one way to do this.  

Select your column in the query editor and add a transform step, like add a prefix (you will replace so it doesn't matter what you choose).  This will generate a Table.TransformColumns step.  Once that is done, replace the "each" part with the "each part below

= Table.TransformColumns(#"Changed Type", {{"Text", each if Text.ToList(_){1} = "." then _ else "Unknown", type text}})

It converts the text string to a list and then checks if the 2nd one (counting starts at 0) is a ".".

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[ryan_mayu]

@Netrelemo 

please provide the sample data and expected output.