CODEWITHSUNDEEP
numberscharacterformulapowerquery
Kerry
Kerry

Reputation: 121

Power Query - remove characters from number values

I have a table field where the data contains our memberID numbers followed by character or character + number strings For example:

My Data
1234567Z1
2345T10
222222T10Z1
111
111A

Should Become
123456
12345
222222
111
111

I want to get just the member number (as shown in Should Become above). I.E. all the digits that are LEFT of the first character. As the length of the member number can be different for each person (the first 1 to 7 digit) and the letters used can be different (a to z, 0 to 8 characters long), I don't think I can SPLIT the field.

Right now, in Power Query, I do 27 search and replace commands to clean this data (e.g. find T10 replace with nothing, find T20 replace with nothing, etc)

Can anyone suggest a better way to achieve this?

I did successfully create a formula for this in Excel...but I am now trying to do this in Power Query and I don't know how to convert the formula - nor am I sure this is the most efficient solution.

=iferror(value(left([MEMBERID],7)),
     iferror(value(left([MEMBERID],6)),
         iferror(value(left([MEMBERID],5)),
             iferror(value(left([MEMBERID],4)),
                 iferror(value(left([MEMBERID],3)),0)
             )
         )
     )
 )

Thanks

Upvotes: 1

Views: 6231

Answers (1)

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4144

There are likely several ways to do this. Here's one way:

  1. Create a query Letters:

let Source = { "a" .. "z" } & { "A" .. "Z" } in Source

  1. Create a query GetFirstLetterIndex:

let Source = (text) => let // For each letter find out where it shows up in the text. If it doesn't show up, we will have a -1 in the list. Make that positive so that we return the index of the first letter which shows up. firstLetterIndex = List.Transform(Letters, each let pos = Text.PositionOf(text, _), correctedPos = if pos < 0 then Text.Length(text) else pos in correctedPos), minimumIndex = List.Min(firstLetterIndex) in minimumIndex in Source

  1. In the table containing your data, add a custom column with this formula:

Text.Range([ColumnWithData], 0, GetFirstLetterIndex([ColumnWithData]))

That formula will take everything from your data text until the first letter.

Upvotes: 3

Related Questions