Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
user180618
Helper I
Helper I

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
AlB
Super User
Super User

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
AlB
Super User
Super User

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors