电源查询 - 从数字值中删除字符

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

我有一个表字段,其中数据包含我们的memberID号后跟字符或字符+数字字符串例如:

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.

我想得到会员编号(如上面的应该显示)。 I.E.所有第一个字符左边的数字。由于会员号码的长度可能因人而异(前1到7位),所用的字母可能不同(a到z,0到8个字符长),我认为我不能分割这个字段。

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)

现在,在Power Query中,我做了27次搜索和替换命令来清理这些数据(例如找到T10替换为什么,找到T20替换为什么,等等)

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.

我在Excel中成功创建了一个公式...但我现在正在尝试在Power Query中执行此操作,我不知道如何转换公式 - 我也不确定这是最有效的解决方案。

=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

1 个解决方案

#1

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

有几种方法可以做到这一点。这是一种方式:

Create a query Letters:

创建查询信件:

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

在源代码中输入Source = {“a”..“z”}&{“A”..“Z”}

Create a query GetFirstLetterIndex:

创建查询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

let Source =(text)=> let //对于每个字母,找出它在文本中显示的位置。如果它没有显示,我们将在列表中有一个-1。使那个积极,以便我们返回显示的第一个字母的索引。 firstLetterIndex = List.Transform(Letters,each let pos = Text.PositionOf(text,_),correctedPos = if pos <0 then Text.Length(text)else pos in correctPos),minimumIndex = List.Min(firstLetterIndex)in minimumIndex在源中

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

在包含您的数据的表中,添加具有以下公式的自定义列:

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

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

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

该公式将从您的数据文本到第一个字母的所有内容。

#1