Number.FromText decimal issue

[CiceroBC]

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?

[v-yulgu-msft]

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

[CiceroBC · Accepted solution]

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_SOURCE	DATE	MAX_TEMP	MIN_TEMP	AVERAGE_TEMP	PRECIPITATION	SNOW	DEPTH
NOAA CF6 Weather Data	11/16/2013	62	41	52	0.87	M	M
NOAA CF6 Weather Data	11/17/2013	59	38	49	0.59	M	M
NOAA CF6 Weather Data	11/18/2013	48	31	40	0	0	0
NOAA CF6 Weather Data	11/19/2013	56	33	45	0	0	0
NOAA CF6 Weather Data	11/20/2013	48	37	43	0.08	0	0
NOAA CF6 Weather Data	11/21/2013	40	27	34	0.1	0.6	0
NOAA CF6 Weather Data	11/22/2013	27	17	22	0.02	0.4	1
NOAA CF6 Weather Data	11/23/2013	20	7	14	0	0	T
NOAA CF6 Weather Data	11/24/2013	28	5	17	T	T	T
NOAA CF6 Weather Data	11/25/2013	39	26	33	T	T	T
NOAA CF6 Weather Data	11/26/2013	31	18	25	0	0	T
NOAA CF6 Weather Data	11/27/2013	23	6	15	0	M	M
NOAA CF6 Weather Data	11/28/2013	32	14	23	0	M	M
NOAA CF6 Weather Data	11/29/2013	35	9	22	0	M	M
NOAA CF6 Weather Data	11/30/2013	48	27	38	0	M	M