Filling average values between irregular report date

[Tom_Y]

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 we can then have a monthly consumption and then we can have a bar chart.

Very much appreicated.

[_AAndrade]

Hi @Tom_Y ,

Here's my solution for your problem:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdAxCoAwDAXQq0jnQpNWra6CpyidqkhBHKQO3t66iEW+kCV58AnfOTEccZ3itlQspCBWZJQmXedlXOeQ9hhiOvPWUuleOpEvz5AuqIw1ONaU/omtMTWYWkz2508L/7TN7R2O7SExYcJ9ssaE62JcF7/q8hc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, ReportDate = _t, MeterType = _t, Reading = _t, CalendarDate = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Client", type text}, {"ReportDate", type date}, {"MeterType", type text}, {"Reading", Int64.Type}, {"CalendarDate", type date}}),
    ReplacedValue = Table.ReplaceValue(ChangedType,"",null,Replacer.ReplaceValue,{"Client", "MeterType"}),
    FilledDown = Table.FillDown(ReplacedValue,{"Client", "ReportDate", "MeterType"}),
    ReadingAvg = 
    Table.AddColumn(FilledDown, "Average", each 
        if [CalendarDate] <> null then
            let
                SelecectRows = Table.SelectRows(FilledDown, each ([Reading] <> null)),
                Average = Table.Group(SelecectRows, {"Client"}, {{"ReadingAvg", each List.Average([Reading]), type nullable number}})
            in
                Average
        else null
    ),
    GetAvg = Table.ExpandTableColumn(ReadingAvg, "Average", {"ReadingAvg"}, {"ReadingAvg"}),
    NewReading = Table.AddColumn(GetAvg, "NewReading", each if [Reading] = null then [ReadingAvg] else [Reading], Int64.Type),
    RemovedColumns = Table.RemoveColumns(NewReading,{"Reading", "ReadingAvg"}),
    ReorderedColumns = Table.ReorderColumns(RemovedColumns,{"Client", "ReportDate", "MeterType", "NewReading", "CalendarDate"})
in
    ReorderedColumns

Final output:

[Tom_Y]

Thanks @_AAndrade !

I'm afraid I need something even more complicated, I want it to increase progressively from 60 (with data) to 62 64 66 ... (fill those without date) and then 76 (with data again), and then e.g. 77 78 79 (without data, average between last data and next data)

[Tom_Y]

Thanks @_AAndrade !

I'm afraid I need something even more complicated, I want it to increase progressively from 60 (with data) to 62 64 66 ... (fill those without date) and then 76 (with data again), and then e.g. 77 78 79 (without data, average between last data and next data)

[Sergii24 · Accepted solution]

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]

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