Forum Discussion
Replace null with average value of above/below value
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!
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
Replace3Zubair_Muhammad Thanks for your very thorough reply!
I tried it and for a while I though it might work, PowerBI was working on about 40 minutes on rendering it but when it was done all that happened was that the columns switched back to their original names? I'm at such a loss here wondering what I'm doing wrong..
- Zubair_MuhammadCould you share your file?
Unfortunately not!
But it's a merger of a merger (if that makes sence) where the first merger is from two different sources, and the second merger is made from the first merger and one of the original sources and aggregated to remove duplicate dates, let me show you:So the first merger looks like this (lets call this one "test"):
DATE: MG TG OW
30/1 0,01 null null
30/1 null 0,5 null
29/1 null null 0,2
29/1 0,18 null null
28/1 null null 0,5
27/1 null 0,1 null
26/1 null null 0,1
26/1 null 0,5 null
26/1 0,42 null null
25/1 null 0,2 null
24/1 0,1 null 0,3
Then for the second merger I made custom tables like this: #"Added Custom" = Table.AddColumn(#"Renamed Columns", "MG", each if [SAMPLING_POINT] = "1" and [NAME] = "MG" then [VALUE] else null) to make it look like this:
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
26/1 0,42 0,5 0,1
25/1 null 0,2 null
24/1 0,1 null 0,3
And at this point I aggregated the columns and only selected the ones I wanted (MG, TG and OW).
So after I tried your code the column names changed from "MG" to "Sum of test.value" (except one column that had no "null's"