Forum Discussion
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_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
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 |