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

Number.FromText decimal issue

I'm having some difficulty with a custom column related to weather data.  I've successfully extracted values for precipitation to segregate actual numbers from an annoying "T" for "trace amount", but am not getting a consistent result when doing the same for snow (yes, I realize that is also precipitation - not my dataset) from "M" for "missing data".  I suspect that the issue has to do with the decimals in the source field, but I'm not sure how to keep the decimal scale in snow.

 

Precipitation:

Example Values: 0.00, T, 0.18

Steps applied (note that the Expanded step filters out error values):

#"Added Custom" = Table.AddColumn(#"Changed Type2", "Precipitation Numbers", each try Number.FromText([PRECIPITATION])),
#"Expanded Precipitation Numbers" = Table.ExpandRecordColumn(#"Added Custom", "Precipitation Numbers", {"Value"}, {"Value"}),

Results: 0, null, 0.18

 

Snow:

Example Values: 0.0, M, 0.1

Steps applied (note that the Expanded step filters out error values):
#"Added Custom1" = Table.AddColumn(#"Changed Type3", "SNOW Number", each try Number.FromText([DEPTH])),
#"Expanded SNOW Number" = Table.ExpandRecordColumn(#"Added Custom1", "SNOW Number", {"Value"}, {"Value"}),
Results: 0, null, 0

 

How can I get an accurate decimal scale in Snow numbers?

1 ACCEPTED SOLUTION

Accepted Solutions
CiceroBC Frequent Visitor
Frequent Visitor

Re: Number.FromText decimal issue

Hi Yuliana,
I'm happy to report that I've resolved my issue. The root problem was an incorrect field reference where
#"Added Custom1" = Table.AddColumn(#"Changed Type3", "SNOW Number", each try Number.FromText([DEPTH])) 
should have been

#"Added Custom1" = Table.AddColumn(#"Changed Type3", "SNOW Number", each try Number.FromText([SNOW])) 

Even though my issue is fixed, here are answers to your questions. This may be something other users would find valuable.

  • What does "T" refer to and what does "M" refer to in actual data values? "T" = "trace amount", "M" = "missing data"
  • What is your Data Source? Weather Data pulled from https://w2.weather.gov/climate/index.php (sample data below)
  • How did you connect to data source? Custom web scrape/ETL process for interpreting data from weather site. FYI - values "T" and "M" are part of the source data, not an artifact of ETL.
  • What is the data type of the weather data [PRECIPITATION] and [DEPTH]? Open text (txt/csv) from web scraper.  No data type designated.

Sample Data:

DATA_SOURCEDATEMAX_TEMPMIN_TEMPAVERAGE_TEMP PRECIPITATIONSNOWDEPTH
NOAA CF6 Weather Data11/16/20136241520.87    M    M
NOAA CF6 Weather Data11/17/20135938490.59    M    M
NOAA CF6 Weather Data11/18/2013483140000
NOAA CF6 Weather Data11/19/2013563345000
NOAA CF6 Weather Data11/20/20134837430.0800
NOAA CF6 Weather Data11/21/20134027340.10.60
NOAA CF6 Weather Data11/22/20132717220.020.41
NOAA CF6 Weather Data11/23/20132071400    T
NOAA CF6 Weather Data11/24/201328517    T    T    T
NOAA CF6 Weather Data11/25/2013392633    T    T    T
NOAA CF6 Weather Data11/26/201331182500    T
NOAA CF6 Weather Data11/27/2013236150    M    M
NOAA CF6 Weather Data11/28/20133214230    M    M
NOAA CF6 Weather Data11/29/2013359220    M    M
NOAA CF6 Weather Data11/30/20134827380    M    M
2 REPLIES 2
v-yulgu-msft Super Contributor
Super Contributor

Re: Number.FromText decimal issue

Hi @CiceroBC,

 

With current description, I was not able to reproduce the same scenario on my side. What does "T" refer to and what does "M" refer to in actual data values? What is your data source? How did you connect to data source? And what is the data type of the weather data [PRECIPITATION] and [DEPTH]? Please provide sample data so that I can test on ny side.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CiceroBC Frequent Visitor
Frequent Visitor

Re: Number.FromText decimal issue

Hi Yuliana,
I'm happy to report that I've resolved my issue. The root problem was an incorrect field reference where
#"Added Custom1" = Table.AddColumn(#"Changed Type3", "SNOW Number", each try Number.FromText([DEPTH])) 
should have been

#"Added Custom1" = Table.AddColumn(#"Changed Type3", "SNOW Number", each try Number.FromText([SNOW])) 

Even though my issue is fixed, here are answers to your questions. This may be something other users would find valuable.

  • What does "T" refer to and what does "M" refer to in actual data values? "T" = "trace amount", "M" = "missing data"
  • What is your Data Source? Weather Data pulled from https://w2.weather.gov/climate/index.php (sample data below)
  • How did you connect to data source? Custom web scrape/ETL process for interpreting data from weather site. FYI - values "T" and "M" are part of the source data, not an artifact of ETL.
  • What is the data type of the weather data [PRECIPITATION] and [DEPTH]? Open text (txt/csv) from web scraper.  No data type designated.

Sample Data:

DATA_SOURCEDATEMAX_TEMPMIN_TEMPAVERAGE_TEMP PRECIPITATIONSNOWDEPTH
NOAA CF6 Weather Data11/16/20136241520.87    M    M
NOAA CF6 Weather Data11/17/20135938490.59    M    M
NOAA CF6 Weather Data11/18/2013483140000
NOAA CF6 Weather Data11/19/2013563345000
NOAA CF6 Weather Data11/20/20134837430.0800
NOAA CF6 Weather Data11/21/20134027340.10.60
NOAA CF6 Weather Data11/22/20132717220.020.41
NOAA CF6 Weather Data11/23/20132071400    T
NOAA CF6 Weather Data11/24/201328517    T    T    T
NOAA CF6 Weather Data11/25/2013392633    T    T    T
NOAA CF6 Weather Data11/26/201331182500    T
NOAA CF6 Weather Data11/27/2013236150    M    M
NOAA CF6 Weather Data11/28/20133214230    M    M
NOAA CF6 Weather Data11/29/2013359220    M    M
NOAA CF6 Weather Data11/30/20134827380    M    M

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: 282 members 3,140 guests
Please welcome our newest community members: