CODEWITHSUNDEEP
excelpowerbipowerquery
DenStudent
DenStudent

Reputation: 928

Power Query - replace all numbers with null

So, I have a datalist of cities saved in Excel, which I want to visualize in PowerBI. However, some of the cities contain a zipcode. This zipcode only exists from numbers.

Is it possible to replace all numbers in that column with "" (nothing)? Or another option is to check if the value starts with a number and then removes the first 4 characters in that cell?

Any ideas how this can be done?

Upvotes: 3

Views: 5233

Answers (3)

Bjoern
Bjoern

Reputation: 458

Just a thought, as this wasn't your original question, but in case you wanted to have the right city instead of the zip code, you could also copy the column, keep only the numbers, mark that as zip code data, get the result back from the Bing Maps API and merge the "city name" result back into the "final" cities column.

Upvotes: 0

ImkeF
ImkeF

Reputation: 1588

You can trim the start of your columns ("Custom") entries using this command:

Table.TransformColumns(NameOfPreviousStep,{{"Custom", each Text.TrimStart(_,List.Transform({0..9}, each Number.ToText(_)))}})

Upvotes: 4

Anand Acharya
Anand Acharya

Reputation: 1

Try this User Defined Function

Alt+F11 to open the VBEditor Insert > Module Then paste the code below in the right panel

Function RemoveNumbers(t As String)
Dim i As Long, 
Dim newString As String

For i = 1 To Len(t)
    If Not IsNumeric(Mid(t, i, 1)) Then
        newString = newString & Mid(t, i, 1)
    End If
Next i
RemoveNumbers = newString
End Function

Assuming your data in A2, A3... put this formula in B2 =RemoveNumbers(A2)

Upvotes: 0

Related Questions