Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Rhodes
Frequent Visitor

Recreating Excel Formula in Power Query

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. 

excel.png

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):

power query table.png

 

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.

  DAX.png

 

Appericate any help.

2 REPLIES 2
Greg_Deckler
Super User
Super User

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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). 

DateTimeRC_CHAVANEAUX_5 minRC_CHAVANEAUX_1-HrRC_CHAVANEAUX_2-HrRC_CHAVANEAUX_3-Hr
9/15/2018 2:550.010.070.290.54
9/15/2018 3:000.010.080.290.61
9/15/2018 3:050.010.080.290.67
9/15/2018 3:100000
9/15/2018 3:150.010.110.30.69
9/15/2018 3:200000
9/15/2018 3:250000
9/15/2018 3:300.010.130.410.71
9/15/2018 3:350000
9/15/2018 3:400000
9/15/2018 3:450.010.220.460.73
9/15/2018 3:500.010.220.460.73
9/15/2018 3:550.020.220.470.72
9/15/2018 4:000.010.210.530.71
9/15/2018 4:050.020.210.590.7
9/15/2018 4:100.020.190.590.69
9/15/2018 4:150.010.190.580.67
9/15/2018 4:200.010.210.580.67
9/15/2018 4:250.010.210.580.67
9/15/2018 4:300.030.280.580.66
9/15/2018 4:350.050.270.550.64
9/15/2018 4:400.020.230.520.59
9/15/2018 4:450.010.240.510.57
9/15/2018 4:500.010.240.510.57
9/15/2018 4:550.010.250.50.56
9/15/2018 5:000.010.320.50.55
9/15/2018 5:050000
9/15/2018 5:100.020.40.50.55
9/15/2018 5:150.030.390.480.53
9/15/2018 5:200.010.370.460.5
9/15/2018 5:250.080.370.460.5
9/15/2018 5:300.020.30.380.42
9/15/2018 5:350.010.280.370.41
9/15/2018 5:400.030.290.360.4
9/15/2018 5:450.010.270.330.38
9/15/2018 5:500.020.270.330.37
9/15/2018 5:550.080.250.310.35
9/15/2018 6:000.070.180.230.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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.