Hi,
Trying to do some math operations to convert DMS latitude and longitude information to decimal coordinates.
He goal is to eventually generate maps with it. The formula is known and not a problem.
I do some sanitation in the DMS text strings, because users typed them a bit differently in the source file, but I massage them to make them into well-formed DMS coordinates in 2 columns. I then perform several steps to convert each column into a decimal latitude and longitude.
Each DMS text string is broken down in 4 column components, all explicitly declared Number.Type.
When trying any math operation in the component columns, I get the Type Error below.
The problem is, even though All my columns are explicitly declared as Number.Type. Power Query is still giving me type conversion errors in all rows, on column [Latdecimal], step #"Added Custom11".
Expression.Error: We cannot apply operator + to types Number and Text.
Here is the code:
let
Source = checklist_locations_qry,
#"Removed Columns" = Table.RemoveColumns(Source,{"City", "State"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",0,"",Replacer.ReplaceValue,{"gps"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","-","",Replacer.ReplaceValue,{"gps"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each ([gps] <> #date(1899, 12, 31) and [gps] <> "")),
#"Distinct Rows" = Table.Distinct(#"Filtered Rows"),
#"Replaced Value2" = Table.ReplaceValue(#"Distinct Rows","#(lf)","|",Replacer.ReplaceText,{"gps"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","T","|",Replacer.ReplaceText,{"gps"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3"," ","",Replacer.ReplaceText,{"gps"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value4", "whitespaces", each List.Count(Text.Split([gps]," "))-1),
#"Replaced Value5" = Table.ReplaceValue(#"Added Custom","Lat.","S",Replacer.ReplaceText,{"gps"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Long.","O",Replacer.ReplaceText,{"gps"}),
#"Added Custom1" = Table.AddColumn(#"Replaced Value6", "latitude", each Text.TrimEnd(Text.BeforeDelimiter([gps],"|"),"S")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "longitude", each Text.TrimEnd(Text.AfterDelimiter([gps],"|"), "O")),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Lathemi", each if Text.At([latitude],0) = "S" then -1 else 1, Number.Type),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Latdeg", each Text.BeforeDelimiter(Text.TrimStart(Text.TrimStart([latitude], "N"),"S"),"°"), Number.Type),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Latmin", each Text.BeforeDelimiter(Text.AfterDelimiter([latitude],"°"),"'"), Number.Type),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "Latsec", each Text.BeforeDelimiter(Text.AfterDelimiter([latitude],"'"),""""), Number.Type),
#"Added Custom7" = Table.AddColumn(#"Added Custom6", "Longhemi", each if Text.At([longitude],0) = "O" then -1 else 1, Number.Type),
#"Added Custom8" = Table.AddColumn(#"Added Custom7", "Longdeg", each Text.BeforeDelimiter(Text.TrimStart(Text.TrimStart([longitude], "O"),"E"),"°"), Number.Type),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "Longmin", each Text.BeforeDelimiter(Text.AfterDelimiter([longitude],"°"),"'"), Number.Type),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Longsec", each Text.BeforeDelimiter(Text.AfterDelimiter([longitude],"'"),""""), Number.Type),
#"Added Custom11" = Table.AddColumn(#"Added Custom10", "Latdecimal", each [Lathemi] + [Latdeg], Number.Type)
in
#"Added Custom11"
