Date blank

[Cocrodile]

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/03/2022 date out 27/03/2022 

date in  16/03/2022 date out 20/03/2022

PS: I have the data for a whole year 

[Cocrodile · Accepted solution]

The solution in power query  is : 

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

🤣

[BA_Pete]

Hi @Cocrodile ,

I'm not sure I get the joke here.

The code you've provided and marked as the solution doesn't actually give you the dates that you asked for. I think your code should be:

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

Pete

[Cocrodile]

we in europe consider the weekend to be Sunday ..
however, I laughed because the solution was very easy

[serpiva64]

Hi,

You can try:

- add a custom column to your date table

- then a calculated column 

date out noblank = if(ISBLANK('Table (2)'[data out]),format(RELATED(Dates[WeekEndingSunday]),"dd/mm/yyyy"),FORMAT('Table (2)'[data out],"dd/mm/yyyy"))

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[BA_Pete]

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