Forum Discussion

Nelleke-NL's avatar
Nelleke-NL
Helper II
2 years ago
Solved

Null value in date column

Hi

I have two date column but in each one there is sometimes the value null.

I need months between [startdatum] and [einddatum].

I have :

= Table.AddColumn(Bron, "duur dossierbehandeling", each if [Einddatum]<>null

then Number.Round(Number.From(([Einddatum] -[Startdatum])/( 365.25 / 12 )),0)

else Number.Round(Number.From((DateTime.Date(DateTime.LocalNow()) -[Startdatum])/( 365.25 / 12)),0))

 

But I miss the function to replace the date in [start date] with the value in [End date] if no date has been entered.

 

I am just an beginner , I'm learning of trying and sometimes asking. And so you can see : dutch and my english is not perfect...Please help me.

 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi Nelleke-NL, I'm not sure wether I understand your needs but try to check this and update with your needs.

     

    IMPORTANT: For future requests, provide sample data as table so we can copy/paste. It is also important to provide expected result based on sample data!

     

    Result

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYzBDcAwCAN34R0JTJo2mQVl/zUagqqWvpDvbMwIlWWwilYqhIuBCLMYCVh2lOU8qIdju2j/yUjk2aMvF6QxJJHP1ZrW/v70bnPXXzdv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Startdatum = _t, Einddatum = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Startdatum", type date}, {"Einddatum", type date}}, "nl-NL"),
    Ad_EinddatumUpdated = Table.AddColumn(ChangedType, "Einddatum Updated", each if [Einddatum] <> null then [Einddatum] else Date.From(DateTime.LocalNow()), type date),
    Ad_StartdatumUpdated = Table.AddColumn(Ad_EinddatumUpdated, "Startdatum Updated", each if [Startdatum] <> null then [Startdatum] else [Einddatum Updated], type text),
    Ad_Months = Table.AddColumn(Ad_StartdatumUpdated, "Months", each 
        Duration.Days( ([Einddatum Updated] - [Startdatum Updated]) / (365.25 / 12) )
  , Int64.Type)
in
    Ad_Months

     

1 Reply

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Nelleke-NL, I'm not sure wether I understand your needs but try to check this and update with your needs.

     

    IMPORTANT: For future requests, provide sample data as table so we can copy/paste. It is also important to provide expected result based on sample data!

     

    Result

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYzBDcAwCAN34R0JTJo2mQVl/zUagqqWvpDvbMwIlWWwilYqhIuBCLMYCVh2lOU8qIdju2j/yUjk2aMvF6QxJJHP1ZrW/v70bnPXXzdv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Startdatum = _t, Einddatum = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Startdatum", type date}, {"Einddatum", type date}}, "nl-NL"),
    Ad_EinddatumUpdated = Table.AddColumn(ChangedType, "Einddatum Updated", each if [Einddatum] <> null then [Einddatum] else Date.From(DateTime.LocalNow()), type date),
    Ad_StartdatumUpdated = Table.AddColumn(Ad_EinddatumUpdated, "Startdatum Updated", each if [Startdatum] <> null then [Startdatum] else [Einddatum Updated], type text),
    Ad_Months = Table.AddColumn(Ad_StartdatumUpdated, "Months", each 
        Duration.Days( ([Einddatum Updated] - [Startdatum Updated]) / (365.25 / 12) )
  , Int64.Type)
in
    Ad_Months