Forum Discussion
Solved
Replace null values by max value by key
I have opened a Dataflow and now I want to fill out missing dates by key.
My data Looks like this :
| id | number | type | INDEX | DATUM |
| 250000 | 0 | 1 | 0 | 02.03.21 |
| 250000 | 1 | 1 | 0 | |
| 250000 | 2 | 1 | 0 | |
| 250000 | 3 | 25 | 0 | |
| 250000 | 4 | 25 | 1 | |
| 250000 | 5 | 25 | 2 | |
| 250000 | 6 | 1 | 0 | |
| 250001 | 0 | 1 | 0 | 26.02.21 |
| 250001 | 1 | 1 | 0 | |
| 250001 | 2 | 1 | 0 | |
| 250001 | 3 | 1 | 0 | |
| 250001 | 4 | 1 | 0 | |
| 250001 | 5 | 1 | 0 | |
| 250001 | 6 | 1 | 0 | |
| 250001 | 7 | 1 | 0 | |
| 250001 | 8 | 1 | 0 | |
| 250001 | 9 | 4 | 0 | |
| 250001 | 10 | 17 | 0 | |
| 250001 | 11 | 22 | 0 | |
| 250002 | 0 | 1 | 0 | 16.03.21 |
| 250002 | 1 | 1 | 0 | |
| 250002 | 2 | 1 | 0 | |
| 250002 | 3 | 25 | 0 | |
| 250002 | 4 | 25 | 1 | |
| 250002 | 5 | 25 | 2 | |
| 250002 | 6 | 1 | 0 |
What I want to achive:
| id | number | type | INDEX | DATUM |
| 250000 | 0 | 1 | 0 | 02.03.21 |
| 250000 | 1 | 1 | 0 | 02.03.21 |
| 250000 | 2 | 1 | 0 | 02.03.21 |
| 250000 | 3 | 25 | 0 | 02.03.21 |
| 250000 | 4 | 25 | 1 | 02.03.21 |
| 250000 | 5 | 25 | 2 | 02.03.21 |
| 250000 | 6 | 1 | 0 | 02.03.21 |
| 250001 | 0 | 1 | 0 | 26.02.21 |
| 250001 | 1 | 1 | 0 | 26.02.21 |
| 250001 | 2 | 1 | 0 | 26.02.21 |
| 250001 | 3 | 1 | 0 | 26.02.21 |
| 250001 | 4 | 1 | 0 | 26.02.21 |
| 250001 | 5 | 1 | 0 | 26.02.21 |
| 250001 | 6 | 1 | 0 | 26.02.21 |
| 250001 | 7 | 1 | 0 | 26.02.21 |
| 250001 | 8 | 1 | 0 | 26.02.21 |
| 250001 | 9 | 4 | 0 | 26.02.21 |
| 250001 | 10 | 17 | 0 | 26.02.21 |
| 250001 | 11 | 22 | 0 | 26.02.21 |
| 250002 | 0 | 1 | 0 | 16.03.21 |
| 250002 | 1 | 1 | 0 | 16.03.21 |
| 250002 | 2 | 1 | 0 | 16.03.21 |
| 250002 | 3 | 25 | 0 | 16.03.21 |
| 250002 | 4 | 25 | 1 | 16.03.21 |
| 250002 | 5 | 25 | 2 | 16.03.21 |
| 250002 | 6 | 1 | 0 | 16.03.21 |
I tried some if and listes but nothing worked so far.
if date = null then get max(date) where ID = ID
else date
Hi LeonardSchwenk ,
It looks like you should just be able to select your [DATUM] column then go to the Transform tab > Fill (dropdown) > Down.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJhCoAgGEOvEv4OcUutzhLd/xqVaFg6BRF8gt8bOw7D4O5lZvNs5NPRusUS5pyrF6heTF9EjZYHhz7zhaFloTC2LPa/w0+D0d4mtQa0BrQGsoZAXqOg0cBh1WjTaM+jdBBSKquAyZwtLFdvrPHfipJXZyLqODloBQet4KAVbBM9Lw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, number = _t, #"type" = _t, INDEX = _t, DATUM = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATUM", type date}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"DATUM"}) in #"Filled Down"Pete
1 Reply
Hi LeonardSchwenk ,
It looks like you should just be able to select your [DATUM] column then go to the Transform tab > Fill (dropdown) > Down.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJhCoAgGEOvEv4OcUutzhLd/xqVaFg6BRF8gt8bOw7D4O5lZvNs5NPRusUS5pyrF6heTF9EjZYHhz7zhaFloTC2LPa/w0+D0d4mtQa0BrQGsoZAXqOg0cBh1WjTaM+jdBBSKquAyZwtLFdvrPHfipJXZyLqODloBQet4KAVbBM9Lw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, number = _t, #"type" = _t, INDEX = _t, DATUM = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATUM", type date}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"DATUM"}) in #"Filled Down"Pete