cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tiago_freire Frequent Visitor
Frequent Visitor

power bi - power query mistaking number for text

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"

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: power bi - power query mistaking number for text

@tiago_freire,

 

You may check the post below.

https://community.powerbi.com/t5/Issues/Table-AddColumn-each-text-expression-type-number-doesn-t-con...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: power bi - power query mistaking number for text

@tiago_freire,

 

You may check the post below.

https://community.powerbi.com/t5/Issues/Table-AddColumn-each-text-expression-type-number-doesn-t-con...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

tiago_freire Frequent Visitor
Frequent Visitor

Re: power bi - power query mistaking number for text

Yes, I figured out the columnt header does not enforce the type or converts to it, requiring an additional operation. 

Explicitly performing the operation solves the problem. 

 

Thanks!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 56 members 1,317 guests
Please welcome our newest community members: