cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marfor Frequent Visitor
Frequent Visitor

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 7
Community Support Team
Community Support Team

Re: Replace null with average value of above/below value

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
marfor Frequent Visitor
Frequent Visitor

Re: 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!

Highlighted
Super User
Super User

Re: Replace null with average value of above/below value

@marfor 

 

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
Try my new Power BI game Cross the River
marfor Frequent Visitor
Frequent Visitor

Re: Replace null with average value of above/below value

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

 

 

Super User
Super User

Re: Replace null with average value of above/below value

Could you share your file?
Try my new Power BI game Cross the River
marfor Frequent Visitor
Frequent Visitor

Re: Replace null with average value of above/below value

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"

 

marfor Frequent Visitor
Frequent Visitor

Re: Replace null with average value of above/below value

@Zubair_Muhammad I maybe should add that one of the sources is just a humungous data set with new measurements for a million different things coming in every day, so I have to do a few steps to just sort out the things that are needed

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 401 members 4,234 guests
Please welcome our newest community members: