Power Query Syntax Text.End

[k1s]

Could somebody tell me the correct syntax for Text.End where I want to specify the text in the column.

I was trying to replace this:

= Table.AddColumn(#"Added Cleaned Class Col", "Year", each if Text.EndsWith([Year SIMS], "N1") then "Year R" else if Text.EndsWith([Year SIMS], "N2") then "Year R" else [Year SIMS] )

With this:

#"Added Conditional Column" = Table.AddColumn(#"Added Cleaned Class Col", "Year", each if Text.End([Year SIMS], "N",2) then "Year R"),

But it didn't work.

Many thanks

 

[Chihiro]

Syntax is quite simple really.

=Text.End("String", #)

Where string is text string to parse and # is number of characters to return.

You can use it like below.

 

[k1s]

Many thanks. How can I used wild card, e.g. logical "...if last but one character is "N" then..." ?

 

[Chihiro]

So you are trying to match any string that contain "N*" in last 2 character of string?

There are multiple ways to do it. Two examples

1. Extract first of last 2 character.
= if Text.Start(Text.End([String], 2), 1) = "N" then "Year R" else "Blah"

2. Use text position.
= if Text.PositionOf([String], "N") >=0 then "Year R" else "Blah"

Note that Text.PositionOf is case sensitive and starting position is zero. If not found, it will return -1.

Edit: There are other ways such as Text.Contains, Custom M for true wild card search etc.

 

[k1s]

Many thanks