Error message using List

[Anonymous]

Hi

I'm creating an M language demo on text functions. Iniitally I show this, a simple extract of the first 8 characters of "Customer Name", which demonstarrtes how this doesn't quite work like Excel LEFT, i.e it returns "Error" when the source string is ess than 8 characters:

As an alternative, I am attempting to use the "List.min" function to get a short version of the Customer name the same length as the shortest Csutomer name, but am getting "Error" instead:

Can anyone help?

Thanks!

[OwenAuger]

Hi @Anonymous 

Not sure if you tried Text.Start? When the number of characters specified is greater than the length of the string, the entire string will be returned without error.

To make this work with Text.Range, you would actually have to transform the list of Customer names into a list of lengths, then take the minimum of those.

Here is the code (formatted for readability):

= Table.AddColumn(
  #"Removed Other Columns",
  "Custom",
  each Text.Range(
    [Customer],
    0,
    List.Min(
      List.Transform(
        #"Removed Other Columns"[Customer],
        Text.Length
      )
    )
  )
)

Since the above code would re-evaluate the same minimum length for every row of the table, you could instead write this to evaluate the minimum length once only:

= let MinLength =
List.Min(
  List.Transform(
    #"Removed Other Columns"[Customer],
    Text.Length
  )
)
in
Table.AddColumn(
  #"Removed Other Columns",
  "Custom",
  each Text.Range(
    [Customer],
    0,
    MinLength
  )
)

Regards,
Owen