Forum Discussion

90_eryka's avatar
90_eryka
Frequent Visitor
5 months ago
Solved

Date Different return wrong value

Dear expert,   I have two tables (Table A: Invoice Data and Table B:GR Data). In Table B, i have group PO number with Max GR Date meanwhile in Table A i have merge it with Table B using PO number. ...
Need Help
Tips and Tricks
  • 90_eryka's avatar
    90_eryka
    5 months ago

    Hi v-achippa vojtechsima ronrsnfld ralf_anton cengizhanarslan ,

     

    Thank you for your assistant and support.

    I'm able to populate the Day Diff correctly with your guide. Here is what i did:

    1. Transform both Doc Date and GR Date column as some of records may contain text

    = Table.TransformColumns(
    #"Reordered Columns",
    {
           {"GR Date", each
                    if _ = null then
                           null
                   else
             let
                 t = Text.From(_),
                       parts = Text.Split(t,"/")
                 in
                      #date(
                      Number.FromText(parts{2}),
                      Number.FromText(parts{0}),
                      Number.FromText(parts{1})
                       ),
                           type date}
                              }
                              )

     

    2. Day Diff

    = Table.AddColumn(Custom3, "Day Diff", each if [GR Date] = null or [Document Date] = null
    then null
    else Duration.Days([GR Date] - [Document Date]))

     

    TQVM

vojtechsima's avatar
vojtechsima
Icon for Super User rankSuper User
5 months ago

Hey, 90_eryka , I would make sure, you're using US format, so first, change your types with Culture with it, like this:

= Table.TransformColumnTypes(Source,{{"Doc Date", type date}, {"GR Date", type date}}, "en-US")

 

Then, when I replicated your code, I didn't have issues.

 

I would also advice rewriting the code like this:

Duration.Days([GR Date] - [Doc Date] )

The function itself will handle nulls for you, so you don't introduce extra logic on top of it.

 

Because you're basically doin:

[GR Date] - [Doc Date]

By default, Nulls works in a way that, for example, when you add or subtract anything, if there's null present anywhere, the result is always null.

 

So nulls are given, now this result give you Duration type, so you use the Duration function to just extract the Days from it.