Forum Discussion
Text.Select or IF and LOOKUP for custom column
Hi user180618
Copy this code into a blank query to see the steps from your initial table with the "other info" column:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPQVTBVSEmsLNZRUDEzMFCK1QGKB1spGIMFFfQVVIxNIaLembkKPlYKRikKGiqGBgaaEEE9Hz2gEWYpCol5KQoqJiATYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Other info" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Other info", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom1", each if List.AnyTrue(List.Transform({"John","JS"},(inner)=> Text.Contains([Other info], inner))) then "John Smith" else if List.AnyTrue(List.Transform({"Kim L","K.L."},(inner)=> Text.Contains([Other info], inner))) then "Kim Lee" else null), //#"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom2", each Text.Combine(List.Select(Text.ToList(Text.Start([Other info],Text.PositionOf([Other info],"d"))), (inner)=> Text.PositionOfAny(inner,{"0".."9"}) >=0 ))), #"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom2", each Text.Select(Text.Start([Other info],Text.PositionOf([Other info],"d")), {"0".."9"}), type number), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom3", each Text.Split([Other info],"$"){1}), #"Split Column by Character Transition" = Table.SplitColumn(#"Added Custom3", "Custom3", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Custom3"}) in #"Split Column by Character Transition"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi user180618
Copy this code into a blank query to see the steps from your initial table with the "other info" column:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPQVTBVSEmsLNZRUDEzMFCK1QGKB1spGIMFFfQVVIxNIaLembkKPlYKRikKGiqGBgaaEEE9Hz2gEWYpCol5KQoqJiATYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Other info" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Other info", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom1", each if List.AnyTrue(List.Transform({"John","JS"},(inner)=> Text.Contains([Other info], inner))) then "John Smith" else if List.AnyTrue(List.Transform({"Kim L","K.L."},(inner)=> Text.Contains([Other info], inner))) then "Kim Lee" else null),
//#"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom2", each Text.Combine(List.Select(Text.ToList(Text.Start([Other info],Text.PositionOf([Other info],"d"))), (inner)=> Text.PositionOfAny(inner,{"0".."9"}) >=0 ))),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom2", each Text.Select(Text.Start([Other info],Text.PositionOf([Other info],"d")), {"0".."9"}), type number),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom3", each Text.Split([Other info],"$"){1}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Added Custom3", "Custom3", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Custom3"})
in
#"Split Column by Character Transition"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thanks AlB, that works.
Just one question: if the Other info column had several names in the same cell, how can I modify the formula to get the Custom columns to show multiple names, days and expenses? For example:
Other info Custom1 Custom2 Custom3 John - 5 days, $600. Kim L: 2d ($100) John Smith, Kim Lee 5, 2 600, 100 Is there a more effective way of doing it than like below?
else if List.AnyTrue(List.Transform({"John","Kim L"},(inner)=> Text.Contains([Other info], inner))) then "John Smith, Kim Lee" else null)It gets trickier because the code above will find the first $ sign, the first "d", "days" , etc.
If you have a separator between the items like for instance && (or something else that won't be confused with actual relevant text)
John - 5 days, $600 && Kim L: 2d ($100)
the best thing would be to split in two columns and use the code above on each of them. Or, if there are many items, use the separator to split into a list and use code similar to above in a function to generate the three custome columns.
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
