Forum Discussion
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/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
The solution in power query is :
if [date out]=null then Date.EndOfWeek ([date in ],Day.Sunday) else [date out])
🤣
5 Replies
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 repNullsTo get this:
Pete
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 !
The solution in power query is :
if [date out]=null then Date.EndOfWeek ([date in ],Day.Sunday) else [date out])
🤣
we in europe consider the weekend to be Sunday ..
however, I laughed because the solution was very easy
