PowerQuery Add Days Formula

[Schwadenfeld]

Hello,

I am new to Power Query and want to to the following.

1. I append two different tables.

2. The first table contains a date, the second table date column is blank

3. this column then should be filled with the date, which is 1 day greater then the latest date in this column

so this "null" / blank value should be replaced, filtering for the latest date in this colum and add 1 day.
In this case the latest date is 17.10.2022 so the date added would be 18.10.2022

Screenshot attached, thanks!

[v-yanjiang-msft]

Hi @Schwadenfeld ,

Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @Schwadenfeld ,

According to your description, here's my solution.

Add a new step in advanced editor.

#"Transform"=Table.ReplaceValue(#"Changed Type",each [Title],each if[Title]=""then Date.AddDays(List.Max(#"Changed Type"[Date]),1) else [Title], Replacer.ReplaceValue, {"Title"})

Get the result:

Before:

After:

Here's the whole M syatax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI3NNA3VNJRUorVQQgYoQsYmoNFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Title = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Title", type text}}),
    #"Transform"=Table.ReplaceValue(#"Changed Type",each [Title],each if[Title]=""then Date.AddDays(List.Max(#"Changed Type"[Date]),1) else [Title], Replacer.ReplaceValue, {"Title"})
in
    #"Transform"

I also attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Schwadenfeld]

Thanks, this goes in the right direction. Yet I want the date to be replaced in the same, already existing column "Date", not creating a new column. So basically two tables are merged, and the second table has no date yet, and this should be checked with the first table and add 1 day to the latest date of the whole column.

[v-yanjiang-msft]

Hi @Schwadenfeld ,

Not very clear. So for the below table, you want the "Date" column instead of "Title" column to be changed to the latest date +1?

Best Regards,
Community Support Team _ kalyj

[Schwadenfeld]

So the "null" value should be = latest date + 1 day

[HotChilli]

I agree with @peter_gorgol with the strategy but I think you want max of the whole column so put :

List.Max(theNameOfThePreviousStep[Date]) instead of [Date] in the formula provided

[Schwadenfeld]

Thank you, I just want to avoid creating a new column, but simply insert the date in the Date column, search for the latest date and add +1 day.

[peter_gorgol]

Could you just remove empty column? Then just simply add new Custom Column and write formula  

Date.AddDays( [Date], 1 )

[Schwadenfeld]

Thank you Peter, the thing is that I want to add the date in the cell itself as seen on my screenshot, not creating another columun, so it needs to refer to the same column