Forum Discussion

user180618's avatar
user180618
Helper I
6 years ago
Solved

Text.Select or IF and LOOKUP for custom column

I'm trying to create some custom columns based on values in another column. For example, below is a column for travel days and expenses for John Smith and Kim Lee, and the custom columns that I would...
Need Help
  • AlB's avatar
    AlB
    6 years ago

    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 

user180618's avatar
user180618
Helper I
6 years ago

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 infoCustom1Custom2Custom3
John - 5 days, $600. Kim L: 2d ($100)John Smith, Kim Lee5, 2600, 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)

 

AlB's avatar
AlB
Community Champion
6 years ago

user180618 

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