Forum Discussion

LeonardSchwenk's avatar
LeonardSchwenk
Frequent Visitor
3 years ago
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 ...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

BA_Pete's avatar
BA_Pete
Super User
3 years ago

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