March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
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
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
94 | |
86 | |
84 | |
76 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |