Replace the 0 with the average of rows

[cvasquez2a_]

Hello comunity,

I am trying to do the following in power query or in dax but I don't know how to achieve it I would appreciate your support.
I have the following table where you will notice that there are some profits with a value of 0. I would like to replace the profit with the average of the previous and next day. If the value of the following day does not exist, replace the 0 with the value of the previous day

So in the end the table should look like this

I would greatly appreciate your support in helping me solve this problem, I've been trying for hours and nothing. Filling down would not help me

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhzaDdGGLKdQdIByf?e=Ro6Kve

[tackytechtom]

Hi @cvasquez2a_ ,

Here a solution in PQ:

Here the M code that you can paste into the advanced editor. Check out the applied steps on the right 🙂

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY1MNE1MFTSUXJ0cgaShgZKsTpIMkZwGTQJY4QWE1QZE4SMGaoMyJrAcFcgaWSMaQ1UxgTTHoiMAaY1EAljA0xrHINdQA7A4hmwhDkWz0B0YPMMWAZoSSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Store = _t, #"accumulated profit" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Store", type text}, {"accumulated profit", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store"}, {{"AllRows", each _, type table [Date=nullable date, Store=nullable text, accumulated profit=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ( [AllRows], "Index", 1 )),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "Store", "accumulated profit", "Index"}, {"Date", "Store", "accumulated profit", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "IndexPrevRow", each [Index] - 1),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "IndexNextRow", each [Index] + 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom2", {"Store", "Index"}, #"Added Custom2", {"Store", "IndexPrevRow"}, "NextRow", JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries", {"Store", "Index"}, #"Merged Queries", {"Store", "IndexNextRow"}, "PrevRow", JoinKind.LeftOuter),
    #"Expanded PrevRow" = Table.ExpandTableColumn(#"Merged Queries1", "PrevRow", {"accumulated profit"}, {"PrevRow.accumulated profit"}),
    #"Expanded NextRow#(cr)#(lf)" = Table.ExpandTableColumn(#"Expanded PrevRow", "NextRow", {"accumulated profit"}, {"NextRow.accumulated profit"}),
    #"Sorted Rows" = Table.Sort(#"Expanded NextRow#(cr)#(lf)",{{"Store", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Added Custom3" = Table.AddColumn(#"Sorted Rows", "accumulated profit new", each if [accumulated profit] = 0 then if [NextRow.accumulated profit] = null then [PrevRow.accumulated profit] else ( [NextRow.accumulated profit] + [PrevRow.accumulated profit] ) / 2 else [accumulated profit]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Index", "IndexPrevRow", "IndexNextRow", "NextRow.accumulated profit", "PrevRow.accumulated profit"})
in
    #"Removed Columns"

Let me kn0w if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/