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 am attempting to create virtual rainfall data for multiple 'sites' using varying proportional combinations of three rain gauges.
My data are held in a few tables/lists in the following formats:
Site list:
Station apportionment table (not yet processed, no data types selected etc):
Daily rain fall data (summarised table from hourly data):
I would like to define rainfall values in the format:
Rainfall at site z = (rainfall at site z gauge 1 * proportion of gauge 1 for site z) + (rainfall at site z gauge 2 * proportion of gauge 2 for site z) + (rainfall at site z gauge 3 * proportion of gauge 3 for site z)
I would like to have daily values to plot up whereby depending on a (single-select) site slicer the appropriate "virtual rainfall data" are plotted.
The current data model is small 1 year of daily data, ~40 sites and 16 rain gauges. If I can find a clean solution I will look to expand this to 150+ sites and 10+ years of rainfall data (potentially at an hourly resolution).
I would prefer to create this using DAX, but could potentially use a Power Query solution that creates a new rainfall dataset which I can directly link to the 'site' list. For a DAX solution, I can't work out how to relate the site list to the gauge apportionments (do I need to unpivot the second table to 3 columns, Site ID, Gauge ID, proportion? Then come up with some calculation that does the above equation?
EDIT:
To elaborate, my main query is: what is the most efficient measure to calculate the 'daily rainfall (mm/day)' for a given site, using the above equation?
For example, at Audley, the three gauges in use are:
Gauge No. | Gauge Proportion |
99093 | 0.625 |
99179 | 0.348 |
3330 | 0.027 |
Thus the estimated rainfall for Audley on a given date is:
Audley Rainfall day n = (rainfall at 99093 on day n * 0.625) + (rainfall at 99179 on day n * 0.348)+ (rainfall at 3330 on day n * 0.027)
As stated above, I need to calculate this for everyday of a ~10 year (hourly) dataset of rainfall, although some gauges do have isolated missing ranges (generally 98%+ complete).
The end goal is a page with a couple plots on it, presenting the rainfall as calculated above against other internal timeseries data. These will be related by date-time and site ID.
Any and all help is very appreciated. Thanks
I would start by transforming the Station Apportionment table so it's in a usable format. Copy the steps beginning with UnpivotColumns into your query.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"Xc+9CsIwFAXgV5FMCqU0/2ZUR8GlgkPpENs7SK9tqRbx7U1uqxSXe0JIvpwUBduNNcKbJYxLbrOQRwCEkDmPQ4XhXOYkJbcupJQyHkyNFpRS0XaqtTGsTAq2h7r1iDManRO8+m54rta5x67fRFmEMaFaLx4hSNEyVTozcYtM9FWDvoFZFf9VF8ZckDoKYamjpRtEHfy9v3Xt6gzD4KsJzEz86QUQ4XHtxqElV/46bt3k2y+sjebUUaotweUH",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [
Source_Name = _t,
SAP_SITE_ID = _t,
First_Station_Name = _t,
Second_Station_Name = _t,
Third_Station_Name = _t,
First_Station_Number = _t,
Second_Station_Number = _t,
Third_Station_Number = _t,
First_State_Proportion = _t,
Second_State_Proportion = _t,
Third_State_Proportion = _t
]
),
ChangeType = Table.TransformColumnTypes(
Source,
{
{"Source_Name", type text},
{"SAP_SITE_ID", Int64.Type},
{"First_Station_Number", Int64.Type},
{"Second_Station_Number", Int64.Type},
{"Third_Station_Number", Int64.Type},
{"First_State_Proportion", type number},
{"Second_State_Proportion", type number},
{"Third_State_Proportion", type number}
}
),
UnpivotColumns = Table.UnpivotOtherColumns(
ChangeType,
{"Source_Name", "SAP_SITE_ID"},
"Attribute",
"Value"
),
SplitColumn = Table.SplitColumn(
UnpivotColumns,
"Attribute",
Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false),
{"Attribute.1", "Attribute.2"}
),
PivotColumns = Table.Pivot(
SplitColumn,
List.Distinct(SplitColumn[Attribute.2]),
"Attribute.2",
"Value"
),
RemoveColumn = Table.RemoveColumns(PivotColumns, {"Attribute.1"}),
FilterNull = Table.SelectRows(RemoveColumn, each ([Station_Number] <> null)),
ChangeType2 = Table.TransformColumnTypes(
FilterNull,
{{"Station_Name", type text}, {"Station_Number", type text}, {"State_Proportion", type number}}
)
in
ChangeType2
You'll need to provide sample data for the Daily Rainfall table (not a screenshot).
Proud to be a Super User!
Hi, @OllieSvT
Not fully sure what is your question, can you elaborate a bit more what you are trying to do?
Sample data and expected output would help tremendously.
Best Regards,
Community Support Team _ Eason
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 |
---|---|
105 | |
101 | |
79 | |
73 | |
65 |
User | Count |
---|---|
141 | |
107 | |
100 | |
82 | |
74 |