cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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 like:

 

Other infoCustom1Custom2Custom3
John - 5 days, $600John Smith5600
JS: 3 days / $350John Smith3350
Kim L: 2d ($100)Kim Lee2100
K.L. - 6d and $400Kim Lee6400

 

I would like to do the following:

- Custom1 to show "John Smith" and "Kim Lee" whenever the Other info column has any variation of "John", "JC", "Kim L" or "KL".

- Custom2 to either Text.Select any numbers that are followed by "days" or "d", or numbers between 1-20 (as 20 is the maximum number of days that John or Kim can enter).

- Custom3 to Text.Select any number preceded by the $ sign, or any number above 50 (as 50 is usually the minimum sum of expenses).

 

I'm not sure if a custom column with Text.Select or a combination of IF and LOOKUP is more suitable here. Any suggestions?

1 ACCEPTED SOLUTION
Super User III
Super User III

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 

SU18_powerbi_badge

View solution in original post

3 REPLIES 3
Super User III
Super User III

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 

SU18_powerbi_badge

View solution in original post

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)

 

@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 

SU18_powerbi_badge

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors