Forum Discussion
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...
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 🙂
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: