Forum Discussion

Tom_Y's avatar
Tom_Y
Advocate II
2 years ago
Solved

Filling average values between irregular report date

Hi all, Clients are sending a Reading to us on irregular basis. (pic1) Is there any way we can fill in the blank in pic 2 with an average values in those date that don't have a report? By doing so...
Need Help
  • Sergii24's avatar
    Sergii24
    2 years ago

    Hi Tom_Y, this is not a simple task in PowerQuery: you would need to build a complex calculation and it might affect a lot refresh time of your report. 

    What exactly are you trying to achieve? If you want to fill in "null" values with the values from the same report, assuming the first row of every new report is not empty, you can try transformation called "Fill->Down":

     

    Otherwise you can load data in PowerBI as it is and then calculate average by creating a measure and using, let's say, Calendar.Date to obtain the number of rows per report.

    I hope it helps, if not don't hesitate to provide more details so we can help you 🙂

Sergii24's avatar
Sergii24
Super User
2 years ago

Hi Tom_Y, this is not a simple task in PowerQuery: you would need to build a complex calculation and it might affect a lot refresh time of your report. 

What exactly are you trying to achieve? If you want to fill in "null" values with the values from the same report, assuming the first row of every new report is not empty, you can try transformation called "Fill->Down":

 

Otherwise you can load data in PowerBI as it is and then calculate average by creating a measure and using, let's say, Calendar.Date to obtain the number of rows per report.

I hope it helps, if not don't hesitate to provide more details so we can help you 🙂

  • Tom_Y's avatar
    Tom_Y
    Advocate II
    2 years ago

    Sergii24 Yes, you're right. It doesn't worth it.

    I change my plan and calculate a "daily consumption", simple division, and then merge calendar, expand, and fill down the "daily consumption". and use "sum" daily consumption in visual.