Forum Discussion
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 we can then have a monthly consumption and then we can have a bar chart.
Very much appreicated.
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 🙂
5 Replies
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:
