Is this a bug or is my function/transform wrong?

[WAB_Reports]

Hello all,

In my PowerQuery, I have two columns that do the following: Age = duration between 2 dates,  JustAgeInDays = text to the left of the "." in Age.  In the sample screen shot below, you can see the highlighted value "0.00:00:00" in Age, which should then translate to "0" in the JustAgeInDays column.  However, it does not, but instead it returns "00:00:00", which in turn throws an "ERROR" when I transform the column into type "Int.64". 

Function to select left of the "."

Function to transform text from above to Int64 

Is this a bug or am I wrong in thinking the 0 should be populated? If it is not a bug, or if there is a better way to in just one function, take the difference in days as a numerical value?

[PaulDBrown · Accepted solution]

@WAB_Reports 

I'm not sure how you have calculated the "Age" (there is a function in Power Query which does this for you).

The code for this is:

 SO if you need to calculate a difference between two date columns, just change the M code for the columns highlighted in yellow. (notice how the type is duration).

In your example, if you select your  "Age" column (set it to type duration first), there is a function which calculates the age in "total days" (here it is with the corresponding code).

[v-kelly-msft · Accepted solution]

Hi  @WAB_Reports ,

Based on my test,it's ok to use "Text.beforedelimiter" function in power query to get the value you need:

But you should be sure that the Age column is a text format.

If your issue still exists ,would you pls upload your .pbix file to onedrive business and share the link with us?

Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[PaulDBrown · Accepted solution]

@WAB_Reports 

I'm not sure how you have calculated the "Age" (there is a function in Power Query which does this for you).

The code for this is:

 SO if you need to calculate a difference between two date columns, just change the M code for the columns highlighted in yellow. (notice how the type is duration).

In your example, if you select your  "Age" column (set it to type duration first), there is a function which calculates the age in "total days" (here it is with the corresponding code).