Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |