Additional Column_ does not end with

[colourfullife]

Hi All,

Need a help.

This is the data i have.

BLOCK :  Q640X /Y750T/ S620B/ S620A . . . .

I want to make a new conditional column that the value doest end with A,B be  PLF..

Q640X and Y750T should be only PLF but it showing everything PLF.

I don't understand why? is there any ideas?

thank you,

CL

[v-frfei-msft]

Hi @colourfullife ,

Please check the online document about Text.EndsWith.

Check if "Hello, World" ends with "World" we use the function like this.

Text.EndsWith("Hello, World", "World")

So in your scenario, we can get the excepted result by these ways.

1. Split the block column and add Conditional column like this.

2. Use text.contains to work on it.

M code for your reference.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjQzMYhQitVBZkWamxqEgFnBZkYGTlhYjsisWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [BLOCK = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"BLOCK", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "BLOCK", "BLOCK - Copy"),
#"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "BLOCK - Copy", Splitter.SplitTextByPositions({0, 1}, true), {"BLOCK - Copy.1", "BLOCK - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"BLOCK - Copy.1", type text}, {"BLOCK - Copy.2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [#"BLOCK - Copy.2"] = "A" then "PLF" else if [#"BLOCK - Copy.2"] = "B" then "PLF" else [BLOCK]),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each if Text.Contains([BLOCK], "A") then "PLF" else if Text.Contains([BLOCK], "B") then "PLF" else [BLOCK]),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.2", each if not Text.EndsWith([#"BLOCK - Copy.2"], "A") then "PLF" else if not Text.EndsWith([#"BLOCK - Copy.2"], "B") then "PLF" else [BLOCK])
in
#"Added Conditional Column1"

[colourfullife]

oh very sorry. it's i want to put PLF in new column if the block column value doesn't end with A,B ..!

I don't understand why they put everything PLF even if i pu the condition there..

Kind Regards,

CL

[v-frfei-msft]

Hi @colourfullife ,

For a string "Hello, World", if we use Text.EndsWith function, the result shoule be "World" not "d". So in your scenario, all rows are "PLF".

[colourfullife]

But in this case,  there is operator options in add conditional column, does not end with..

this function term "end" also all letter? not the one letter of at the end?

[v-frfei-msft · Accepted solution]

Hi @colourfullife ,

As the step in your picture. The corresponding M code is like this. Every action you take will generate M code in power query. You can check the code in Advanced Editor.

= Table.AddColumn(#"Changed Type", "Custom", each if not Text.EndsWith([BLOCK], "A") then "PLF" else if not Text.EndsWith([BLOCK], "B") then "PLF" else null)

As I said just now, We cannot use Text.EndsWith here. Instead, Text.End should work here. We can create a custom column like this.

if Text.End([BLOCK], 1) = "A" then "PLF" else if Text.End([BLOCK], 1) = "B" then "PLF" else null

 

M code for your reference as well.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjQzMYhQitVBZkWamxqEgFnBZkYGTlhYjsisWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [BLOCK = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BLOCK", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if not Text.EndsWith([BLOCK], "A") then "PLF" else if not Text.EndsWith([BLOCK], "B") then "PLF" else null),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if Text.End([BLOCK], 1) = "A" then "PLF" else if Text.End([BLOCK], 1) = "B" then "PLF" else null)
in
    #"Added Custom"