Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Greetings.
I am developing a tool to review historical rainfall data (5-minute timesteps) for 30 rainfall guages. One component of the tool is to compare the cumulative observed rainfall depth vs duration of an observed rainfall event to NOAA depth-duration curves for the region the rain guages are in.
I have been working through several workflows, the one that gets me the furtherst is as follows:
Manually creating duration columns and associated summation formulas in each rain guage csv file.
the formulas for each duration column determines if rainfall occurred at each timestep (if b1>0) and then sums the following 11 rows in Column B to calculate the cumulative rainfall depth observed over 1 hour starting when the rainfall began. This same formula is repeated for the other duration columns (Columns C-J) with the summation being adjusted to match the corresponding duration (2-hour, 3, hour, 6-hour, 12-hour, etc).
This is then imported into power bi and with a couple of querys i get this table (filtered to a time period with rainfall):
This workflow will get the job done but it will require manual editing of each rain guage csv to import updated data into the tool. Is it possible to recreate the duration summation formulas, currently performed in excel, in power query as the data is imported into power bi? this would significantly streamline the workflow of the inital effort of loading the tool with historical data as well as future quarterly updates.
I was able to recreate these summation formulas in the table using the DAX experssion shown below but these columns can not be unpivoted to get the data in the format shown in the second image above.
Appericate any help.
Is it at all possible to post your data (as text/CSV) or PBIX file and/or the formulas you use in Excel? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490. That being said, if it is Power Query magic that you are looking for, @ImkeF may be able to assist you but will likely ask for similar information.
Below is an example of the rainfall data with the 1, 2, and 3 hour duration columns for one day. (copying the dataset sufficent to recreate the duration summations out to 4 days exceeded the message charactor limits but the process should be the same for any duration).
DateTime | RC_CHAVANEAUX_5 min | RC_CHAVANEAUX_1-Hr | RC_CHAVANEAUX_2-Hr | RC_CHAVANEAUX_3-Hr |
9/15/2018 2:55 | 0.01 | 0.07 | 0.29 | 0.54 |
9/15/2018 3:00 | 0.01 | 0.08 | 0.29 | 0.61 |
9/15/2018 3:05 | 0.01 | 0.08 | 0.29 | 0.67 |
9/15/2018 3:10 | 0 | 0 | 0 | 0 |
9/15/2018 3:15 | 0.01 | 0.11 | 0.3 | 0.69 |
9/15/2018 3:20 | 0 | 0 | 0 | 0 |
9/15/2018 3:25 | 0 | 0 | 0 | 0 |
9/15/2018 3:30 | 0.01 | 0.13 | 0.41 | 0.71 |
9/15/2018 3:35 | 0 | 0 | 0 | 0 |
9/15/2018 3:40 | 0 | 0 | 0 | 0 |
9/15/2018 3:45 | 0.01 | 0.22 | 0.46 | 0.73 |
9/15/2018 3:50 | 0.01 | 0.22 | 0.46 | 0.73 |
9/15/2018 3:55 | 0.02 | 0.22 | 0.47 | 0.72 |
9/15/2018 4:00 | 0.01 | 0.21 | 0.53 | 0.71 |
9/15/2018 4:05 | 0.02 | 0.21 | 0.59 | 0.7 |
9/15/2018 4:10 | 0.02 | 0.19 | 0.59 | 0.69 |
9/15/2018 4:15 | 0.01 | 0.19 | 0.58 | 0.67 |
9/15/2018 4:20 | 0.01 | 0.21 | 0.58 | 0.67 |
9/15/2018 4:25 | 0.01 | 0.21 | 0.58 | 0.67 |
9/15/2018 4:30 | 0.03 | 0.28 | 0.58 | 0.66 |
9/15/2018 4:35 | 0.05 | 0.27 | 0.55 | 0.64 |
9/15/2018 4:40 | 0.02 | 0.23 | 0.52 | 0.59 |
9/15/2018 4:45 | 0.01 | 0.24 | 0.51 | 0.57 |
9/15/2018 4:50 | 0.01 | 0.24 | 0.51 | 0.57 |
9/15/2018 4:55 | 0.01 | 0.25 | 0.5 | 0.56 |
9/15/2018 5:00 | 0.01 | 0.32 | 0.5 | 0.55 |
9/15/2018 5:05 | 0 | 0 | 0 | 0 |
9/15/2018 5:10 | 0.02 | 0.4 | 0.5 | 0.55 |
9/15/2018 5:15 | 0.03 | 0.39 | 0.48 | 0.53 |
9/15/2018 5:20 | 0.01 | 0.37 | 0.46 | 0.5 |
9/15/2018 5:25 | 0.08 | 0.37 | 0.46 | 0.5 |
9/15/2018 5:30 | 0.02 | 0.3 | 0.38 | 0.42 |
9/15/2018 5:35 | 0.01 | 0.28 | 0.37 | 0.41 |
9/15/2018 5:40 | 0.03 | 0.29 | 0.36 | 0.4 |
9/15/2018 5:45 | 0.01 | 0.27 | 0.33 | 0.38 |
9/15/2018 5:50 | 0.02 | 0.27 | 0.33 | 0.37 |
9/15/2018 5:55 | 0.08 | 0.25 | 0.31 | 0.35 |
9/15/2018 6:00 | 0.07 | 0.18 | 0.23 | 0.28 |
The "DateTime" and "RC_Chavaneaux_5min" columns are the original data. The other 3 columns are manually created using the Excel Formulas below:
1- hr: =+IF($B4>0,SUM($B4:$B15),)
2-hr: =+IF($B4>0,SUM($B4:$B27),)
3-hr: =+IF($B4>0,SUM($B4:$B39),)
6-hr: =+IF($B4>0,SUM($B4:$B75),)
12-hr: =+IF($B4>0,SUM($B4:$B147),)
24-hr: =+IF($B4>0,SUM($B4:$B279),)
2day: =+IF($B4>0,SUM($B4:$B567),)
4day: =+IF($B4>0,SUM($B4:$B1155),)
Dax Expression is below for 1 hour summation. (note switching to 15 min data once workflow is finalized):
1-Hr Rainfall = var currentDateTime = 'RG Data 15 min 8-01-2018-10-31-2018'[DateTime_Number] var currentGauge = 'RG Data 15 min 8-01-2018-10-31-2018'[Rain Gauge] RETURN if('RG Data 15 min 8-01-2018-10-31-2018'[Rainfall (in)]>0,CALCULATE(SUM('RG Data 15 min 8-01-2018-10-31-2018'[Rainfall (in)]), ALL('RG Data 15 min 8-01-2018-10-31-2018'), 'RG Data 15 min 8-01-2018-10-31-2018'[Rain Gauge] = currentGauge, 'RG Data 15 min 8-01-2018-10-31-2018'[DateTime_Number] >= currentDateTime && 'RG Data 15 min 8-01-2018-10-31-2018'[DateTime_Number] <= currentDateTime +0.03125))
Is it easier to convert the DAX expressions to something power query can use?
Thanks
User | Count |
---|---|
141 | |
70 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |