Forum Discussion
Replace null with average value of above/below value
Hi guys,
I'm quite new to the program and need some help:
In my Query Editor I have a table with say six columns. The first one is dates for a 30 day period and in the columns next to that are values of measurements of different substances taken on any date. Not all measurements are done each days, so in several columns I'll have several rows of null. I would like to either 1. replace those nulls with the average value of the closest row above and beneath that has a set value, or 2. replace the nulls with the highest value from the closest row above or below that has a set value (this can vary within each column so fill up/down won't work)
Does anyone know if this is possible?
Thanks in advance :)
7 Replies
v-piga-msftResident Rockstar
Hi marfor ,
To understand your scenario better, please share some data sample and your desired ouptut so that we could give further advice.
In addition, do you want to achieve that only wiht power query or could accept dax?
If you only want to achieve that with power query, hope ImkeF could give some advice.
Best Regards,
Cherry
DAX will also be fine.
Example:
DATE: MG TG OW
30/1 0,01 0,5 null
29/1 0,18 null 0,2
28/1 null null 0,5
27/1 null 0,1 null <---For MG here the null should be replaced with either 0,42 or 0,3(avrg.)
26/1 0,42 0,5 0,1
25/1 null 0,2 null
24/1 0,1 null 0,3 <---- For MG here the null should be replaced with either 0,42 or 0,26(avrg)
Does that make it clearer? So I can't just juse fill up/down because for example the 0,42 in the MG column must fill both up AND down if I am to fill with the largest of either value above/below. But also if there is a solution filling with average of those two that is also fine. Whichever is easiest.
Thanks for your reply!
- Zubair_Muhammad
Community Champion
Here is one possibility. But might be slow.
Please see attached file as well.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5JCgAxCAS/Ip49jEsgeUvw/98Ys8g4YF+q6cI5EUzJRgMk5EiLoFNy27wvSCilUDhsTz4sF/NfwwubXD+X5oF010Ef+U+covsL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"DATE:" = _t, MG = _t, TG = _t, OW = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE:", Int64.Type}, {"MG", Int64.Type}, {"TG", Int64.Type}, {"OW", Int64.Type}}), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"DATE:", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"DATE:", "DATE"}}), myfunction=(PrevStep as table,ColName as text,mydate)=> let mytable=Table.SelectColumns(PrevStep,{"DATE",ColName}) in let RA=Record.Field(Table.Max(Table.SelectRows(mytable,(x)=> x[DATE] < mydate and (Record.Field(x,ColName)<>null)),"DATE"),ColName), RB=Record.Field(Table.Min(Table.SelectRows(mytable,(x)=> x[DATE] > mydate and (Record.Field(x,ColName)<>null)),"DATE"),ColName) in List.Average( List.RemoveNulls( List.Transform( List.Positions( {RA,RB} ), each try {RA,RB}{_} otherwise null ) )), Replace1 = Table.ReplaceValue(#"Renamed Columns",null,each myfunction(#"Renamed Columns","MG",[DATE]),Replacer.ReplaceValue,{"MG"}), Replace2 = Table.ReplaceValue(Replace1,null,each myfunction(Replace1,"TG",[DATE]),Replacer.ReplaceValue,{"TG"}), Replace3 = Table.ReplaceValue(Replace2,null,each myfunction(Replace1,"OW",[DATE]),Replacer.ReplaceValue,{"OW"}) in Replace3
