Forum Discussion
Fill Up and Fill Down row values based on conditions
Hi everyone.
Seeking help in querying my table.
My table currently looks like this
| Employee | Date | Tagging | Credits | Bal |
| AAA | null | Start | 100 | 100 |
| AAA | 10/1/2021 | Accrual | 8 | 108 |
| AAA | 10/27/2021 | Taken | -4 | 104 |
| AAA | null | End | 104 | 104 |
I need the dates to be indicated in the Date column instead of Null. In such a way that if the tagging is "Start" I will obtain the date from the row BELOW it, and if the tagging is "End" I will obtain the date from the row ABOVE it. It will look like this:
| Employee | Date | Tagging | Credits | Bal |
| AAA | 10/1/2021 | Start | 100 | 100 |
| AAA | 10/1/2021 | Accrual | 8 | 108 |
| AAA | 10/27/2021 | Taken | -4 | 104 |
| AAA | 10/27/2021 | End | 104 | 104 |
Hi Anonymous
This query will do it
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRyivNyQFSwSWJRSVA2tDAAErG6sCUGBroG+obGRgZAtmOyclFpYkgHRZgGQtUdUbmMIUhidmpeUBa1wQsY4KkDmqla14KVAqqIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Date = _t, Tagging = _t, Credits = _t, Bal = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Tagging", type text}, {"Credits", Int64.Type}, {"Bal", Int64.Type}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-US"), #"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Date] is null then if [Tagging] = "Start" then #"Changed Type with Locale"[Date]{[Index]+1} else #"Changed Type with Locale"[Date]{[Index]-1} else [Date]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date", "Index"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Employee", "Date", "Tagging", "Credits", "Bal"}) in #"Reordered Columns"Regards
Phil
2 Replies
- PhilipTreacy
Super User
Hi Anonymous
This query will do it
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRyivNyQFSwSWJRSVA2tDAAErG6sCUGBroG+obGRgZAtmOyclFpYkgHRZgGQtUdUbmMIUhidmpeUBa1wQsY4KkDmqla14KVAqqIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Date = _t, Tagging = _t, Credits = _t, Bal = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Tagging", type text}, {"Credits", Int64.Type}, {"Bal", Int64.Type}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-US"), #"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Date] is null then if [Tagging] = "Start" then #"Changed Type with Locale"[Date]{[Index]+1} else #"Changed Type with Locale"[Date]{[Index]-1} else [Date]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date", "Index"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Employee", "Date", "Tagging", "Credits", "Bal"}) in #"Reordered Columns"Regards
Phil