Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

Replace the 0 with the average of rows

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

Super User
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.!AiUZ0Ws7G26RhzaDdGGLKdQdIByf?e=Ro6Kve

Screen Capture #909.png

Super User
Super User

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 🙂

    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"})
    #"Removed Columns"


Let me kn0w if this helps 🙂



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn


Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.


Fabric certifications survey

Certification feedback opportunity for the community.