Cannot Convert value '' of type Text to number

[Anonymous]

HI All,

I'm using the Dataset from below link

UNLOCODE DATASET

Essentially what I'm trying to do is convert the Coordinates to Latitude and Longitude. Got the basic formula from below link excel file and modified it to suit the UNECE format

PowerBI LAT LONG Converter

I'm getting the error specified as subject title for the same and don't know what to do

LATITUDE = 
IF (
    ISBLANK ( [Coordinates] ),
    BLANK (),
    (
        ( LEFT ( [Coordinates], 2 ) * 1 )
            + ( MID ( [Coordinates], 3, 2 ) * 1 )
                / 60
    )
        * IF ( MID ( [Coordinates], 5, 1 ) = "S", -1, 1 )
)

LONGITUDE =
IF (
    ISBLANK ( [Coordinates] ),
    BLANK (),
    (
        ( MID ( [Coordinates], 7, 3 ) * 1 )
            + (
                ( MID ( [Coordinates], 10, 2 ) * 1 )
                    / 60
            )
    )
        * IF ( RIGHT ( [Coordinates], 1 ) = "W", -1, 1 )
)

[v-yuezhe-msft]

@Anonymous,

Please add custom columns in Power BI Desktop query editor.

=if [Coordinates]= "" then "" else (Number.FromText(Text.Start([Coordinates],2))*1+(Number.FromText(Text.Middle([Coordinates], 2, 2))*1)/60)*(if Text.Middle([Coordinates], 4, 1)="S" then -1 else 1)

=if [Coordinates]= "" then "" else (Number.FromText(Text.Middle([Coordinates], 6, 3))*1+(Number.FromText(Text.Middle([Coordinates], 9, 2))*1)/60)*(if Text.End([Coordinates], 1)="W" then -1 else 1)

Regards,
Lydia

[Anonymous]

Hi Lydia,

Thanks your solution works but I still ended up scrapping it because I realised that the data is not completely clean and error free. Ended up using Customs Columns after the query was done with DAX to be able to do what I need it to. Essentially seems sometimes the Coordinates are 11 characters long, sometimes 12 and sometimes 13. so I have to account for seconds as well where available. This is what I did

Latitude

LDeg = LEFT('code-list'[Coordinates],2)

---

LMin = MID('code-list'[Coordinates],3,2)

---

LSec = 
VAR
secs = MID('code-list'[Coordinates],5,1)

Return
IF(ISTEXT(secs),"00",IF(LEN(secs)=1,"0"&secs,secs))

---

LDir = 
VAR
STpoint =
FIND ("N",'code-list'[Coordinates],1,
    FIND ( "S", 'code-list'[Coordinates], 1, BLANK () )
)

Return
MID('code-list'[Coordinates],STpoint,1)

---

Latitude = ('code-list'[LDeg]+('code-list'[LMin]/60)+('code-list'[LSec]/3600))*IF(UPPER('code-list'[LDir])="S",-1,1)

Longitude

LoDeg = 
VAR
STpoint =
FIND ("N",'code-list'[Coordinates],1,
    FIND ( "S", 'code-list'[Coordinates], 1, BLANK () )
)

Return
MID('code-list'[Coordinates],STpoint+2,3)

---

LoMin = 
VAR
STpoint =
FIND ("N",'code-list'[Coordinates],1,
    FIND ( "S", 'code-list'[Coordinates], 1, BLANK () )
)

Return
MID('code-list'[Coordinates],STpoint+5,2)

---

LoSec = 
VAR
STpoint =
FIND ("N",'code-list'[Coordinates],1,
    FIND ( "S", 'code-list'[Coordinates], 1, BLANK () )
)

VAR
secs = MID('code-list'[Coordinates],STpoint+8,1)

Return
IF(ISTEXT(secs),"00",IF(LEN(secs)=1,"0"&secs,secs))

---

LoDir = RIGHT('code-list'[Coordinates],1)

---

Longitude = ('code-list'[LoDeg]+('code-list'[LoMin]/60)+('code-list'[LoSec]/3600))*IF(UPPER('code-list'[LoDir])="W",-1,1)

[Anonymous · Accepted solution]

I went off and did some studying and put this together. Does it all in Power Query now

let
    Source = Csv.Document(Web.Contents("https://datahub.io/core/un-locode/r/code-list.csv"),[Delimiter=",", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Change", type text}, {"Country", type text}, {"Location", type text}, {"Name", type text}, {"NameWoDiacritics", type text}, {"Subdivision", type text}, {"Status", type text}, {"Function", type text}, {"Date", Int64.Type}, {"IATA", type text}, {"Coordinates", type text}, {"Remarks", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Change", "Remarks", "IATA", "Date", "Function", "Status", "Subdivision", "Name"}),
    #"Add UNLOCODE" = Table.AddColumn(#"Removed Columns", "UN LOCODE", each [Country] & [Location]),
    #"Move UNLOCODE to Start" = Table.ReorderColumns(#"Add UNLOCODE",{"UN LOCODE", "Country", "Location", "NameWoDiacritics", "Coordinates"}),
    #"Remove Country and Location" = Table.RemoveColumns(#"Move UNLOCODE to Start",{"Country", "Location"}),
    #"Renamed Name col" = Table.RenameColumns(#"Remove Country and Location",{{"NameWoDiacritics", "Name"}}),
    #"Remove Blank Coordinates" = Table.SelectRows(#"Renamed Name col", each ([Coordinates] <> "")),
    #"Add LEN" = Table.AddColumn(#"Remove Blank Coordinates", "LEN", each Text.Length([Coordinates])),
    #"Add LAT split" = Table.AddColumn(#"Add LEN", "LAT split", each Text.Start([Coordinates],
Text.PositionOf([Coordinates], " "))),
    #"Add LONG split" = Table.AddColumn(#"Add LAT split", "LONG split", each Text.Trim(
Text.Middle([Coordinates],
Text.PositionOf([Coordinates]," ")
,10)
)),
    #"Add LDeg" = Table.AddColumn(#"Add LONG split", "LDeg", each Number.FromText(
Text.Start([LAT split],2)
)),
    #"Add LMin" = Table.AddColumn(#"Add LDeg", "LMin", each Number.FromText(
Text.Range([LAT split],2,2)
)),
    #"Add LSec ini" = Table.AddColumn(#"Add LMin", "LSec ini", each Text.Range([Coordinates],4,1)),
    #"Add LSec" = Table.AddColumn(#"Add LSec ini", "LSec", each if [LSec ini] = "N" or [LSec ini] = "S"
then
0
else
Number.FromText([LSec ini])),
    #"Add LDir" = Table.AddColumn(#"Add LSec", "LDir", each if
[LSec ini] = "N"
then
1
else
-1),
    #"Added Custom" = Table.AddColumn(#"Add LDir", "LAT", each ([LDeg] +
([LMin]/60) +
([LSec]/3600)
) *
[LDir]),
    #"Remove all LAT calc cols" = Table.RemoveColumns(#"Added Custom",{"LDeg", "LMin", "LSec ini", "LSec", "LDir", "LAT split"}),
    #"Add LoDeg" = Table.AddColumn(#"Remove all LAT calc cols", "LoDeg", each Number.FromText(
Text.Start([LONG split],2)
)),
    #"Add LoMin" = Table.AddColumn(#"Add LoDeg", "LoMin", each Number.FromText(
Text.Range([LONG split],2,2)
)),
    #"Add LoSec" = Table.AddColumn(#"Add LoMin", "LoSec", each if
Text.Length([LONG split]) = 7
then
Number.FromText(
Text.Range([LONG split],4,2)
)
else
Number.FromText(
Text.Range([LONG split],4,1)
)),
    #"Add LoDir" = Table.AddColumn(#"Add LoSec", "LDir", each if
Text.End([LONG split],1) = "W"
then
-1
else
1),
    #"Add LONG" = Table.AddColumn(#"Add LoDir", "LONG", each ([LoDeg] +
([LoMin]/60) +
([LoSec]/3600)) *
[LDir]),
    #"Remove all other columns" = Table.RemoveColumns(#"Add LONG",{"LONG split", "LoDeg", "LoMin", "LoSec", "LDir", "Coordinates", "LEN"})
in
    #"Remove all other columns"