2

This is not a call for assistance, but more for some advice. I have a fully functioning code below and wondered if it could be streamlined.

= Table.AddColumn(
#"Renamed Columns", 
"Company Name", 
each if Text.Length([Person Surname]) > 20 or 
Text.Contains([Person Surname], "Ltd") or 
Text.Contains([Person Surname], "Limited") or 
Text.Contains([Person Surname], "Llp") then [Person Surname]
 else "")

Namingly I am looking at the text.contains parameter. I was wondering if, rather than having 3 separate queries I could just use 1 with something like {"Ltd", "Limited", "LLP"}. This was should I want to add any further parameters down the line I could simply add them into the brace's.

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Peter Mogford
  • 478
  • 1
  • 4
  • 15
  • 1
    Look here. You need to use `if Text.Length([Person Surname]) > 20 or List.AnyTrue(List.Transform({"Ltd","Limited","Llp"}, (x) => Text.Contains([Person Surname], x))) = true then [Person Surname] else null` source: **[How to search multiple strings in a string?](https://stackoverflow.com/a/35324537/8162520)** – Mayukh Bhattacharya Jul 05 '23 at 10:46
  • 1
    @MayukhBhattacharya, IMO the nested `List.Transform()` is redundant if we can do it in one sweep though. Either way, like in many other instances; There is more than one way to skin a cat! – JvdV Jul 05 '23 at 11:07
  • @JvdV Sir absolutely agreed. Understood the use `List.MatchesAny` – Mayukh Bhattacharya Jul 05 '23 at 11:08

1 Answers1

4

You can use the List.MatchesAny function:

enter image description here

if Text.Length([Person Surname]) > 20 or List.MatchesAny({"Ltd","Limited","Llp"}, (s)=> Text.Contains([Person Surname], s)) then [Person Surname] else null
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    This is perfect, will so much easier to manage going forward should anymore parameters need adding. – Peter Mogford Jul 05 '23 at 13:47
  • I am trying to replicate this process to remove the values from `Person Surname` after they have been passed over to the new Company Name Column. However when my makes all of the Surnames an Error. `= Table.TransformColumns( #"Added Custom", {{"Person Surname", each if Text.Length(_)>20 or List.MatchesAny({"Ltd","Limited","Llp","T/a","Plc"}, (s)=> Text.Contains([Person Surname], s)) then "" else _}})` – Peter Mogford Jul 06 '23 at 08:50