Transforming character/number column

[Anonymous]

I have the column "Location" in my data model.  I need to separate it like this:

Column A: if Location starts with a number, I need all the numbers before the first letter

Column B: I need the first letter and each letter to follow before the next number

Column C: I need all the numbers after the last letter

An example below of correct results.

I would like to accomplish this in the Power Query Editor.  Any suggestions?

[v-piga-msft]

Hi @Anonymous,

Have you solved your problem with the solution from @ChrisMendoza?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[ChrisMendoza · Accepted solution]

@Anonymous,

I used a similar approach in the following post, https://community.powerbi.com/t5/Desktop/Replace-character-only-at-the-beginning-of-string-using-Power/m-p/465835#M216286, to help in a possible solution.

    // Converts the string into a list 
LocationToList = Table.AddColumn(#"Changed Type", "LocationToList", each Text.ToList([Location])),
    // Finds the Nth numbers in the beginning of the list
StartsWithNNums = Table.AddColumn(LocationToList, "StartsWithNNums", each List.FirstN([LocationToList], each Value.Is(Value.FromText(_), type number))),
    // Finds the Nth numbers at the end of the list
EndsWithNNums = Table.AddColumn(StartsWithNNums, "EndsWithNNums", each List.LastN([LocationToList], each Value.Is(Value.FromText(_), type number))),
    // Converts back into a string
ColumnA = Table.AddColumn(EndsWithNNums, "ColumnA", each Text.Combine([StartsWithNNums])),
    // Converts back into a string
ColumnC = Table.AddColumn(ColumnA, "ColumnC", each Text.Combine([EndsWithNNums])),
    // Uses @ImkeF solution
ColumnB = Table.AddColumn(ColumnC, "ColumnB", each Text.Remove([Location], List.Transform({48..57}, each Character.FromNumber(_))))
in
    ColumnB

@ImkeF solution that I modified for your needs found at https://community.powerbi.com/t5/Desktop/A-Simple-Method-to-Filter-Text-Strings-in-Query-Editor/td-p/87553.

Those add columns should return the the following for your sample: