Skip to main content
cancel
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.

Reply
cvasquez2a_
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

 

cvasquez2a__1-1681965925487.png

 

So in the end the table should look like this

 

cvasquez2a__0-1681965908594.png

 

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
Ahmedx
Super User
Super User


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

Screen Capture #909.png

tackytechtom
Super User
Super User

Hi @cvasquez2a_ ,

 

Here a solution in PQ:

tackytechtom_0-1681967644277.png

 

 

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/



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

#proudtobeasuperuser 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.