Forum Discussion
Additional Column_ does not end with
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
Hi Anonymous ,
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"
5 Replies
Hi Anonymous ,
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"Hi Anonymous ,
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".