Forum Discussion
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?
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
2 Replies
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
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
