Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 info | Custom1 | Custom2 | Custom3 |
John - 5 days, $600 | John Smith | 5 | 600 |
JS: 3 days / $350 | John Smith | 3 | 350 |
Kim L: 2d ($100) | Kim Lee | 2 | 100 |
K.L. - 6d and $400 | Kim Lee | 6 | 400 |
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?
Solved! Go to Solution.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
13 | |
13 | |
13 | |
12 |