Replace null values by max value by key

[LeonardSchwenk]

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

[BA_Pete · Accepted solution]

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