cancel
Showing results 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

2 REPLIES 2
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

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 🙂

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]}}),
#"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"}),
#"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 🙂

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

Announcements

#### 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.

Top Solution Authors
Top Kudoed Authors