Forum Discussion

Cocrodile's avatar
Cocrodile
Icon for Helper I rankHelper I
4 years ago
Solved

Date blank

Hello I have this table :   Can I ask you for a function that puts me where the empty values are on the last day of that week? I would like to have the results of the 2 dates: date in  22/...
Need Help
  • Cocrodile's avatar
    Cocrodile
    4 years ago

    The solution in power query  is : 

    if [date out]=null then Date.EndOfWeek ([date in ],Day.Sunday) else [date out])

    🤣

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
4 years ago

Hi Cocrodile ,

 

You can use an edited replace step like this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRNzDWNzIwMlLSUTIyg3NidYByRkhyYBFDZNWGliiqDc1QVMcCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"data in" = _t, #"data out" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"data in", type date}, {"data out", type date}}),

    repNulls = Table.ReplaceValue(chgTypes,null, each Date.EndOfWeek([data in], Day.Monday),Replacer.ReplaceValue,{"data out"})
in
    repNulls

 

 

To get this:

 

Pete