Forum Discussion

Advocate II

9 years ago

Solved

custom column error

Hi I created a column with date difference, like this: But there is an error. The difference is 30 days, not 29. I tried this Thanks for some help eduardo

Need Help

v-qiuyu-msft
9 years ago
Hi eduardo,

In Query Editor, when we create a calculated column by using 'Add Custom Column', we need to write formula use Power Query Formula Language. In your scenario, to calculate the difference between start date and end date with day interval, you can use Duration.Days() and Duration.From() functions like below, you can also plus "1" at the end of the formula. For more information about function, see: Duration functions.

=Duration.Days(Duration.From([End date]-[Start Date]))

In your scenario, if you minus directly, it will return values which are not numbers, so you cann't plus '1'. But you can change this column data type to number then create another column to plus '1'.

Best Regards,
Qiuyun Yu

v-qiuyu-msft

Community Support

9 years ago

Hi eduardo,

In Query Editor, when we create a calculated column by using 'Add Custom Column', we need to write formula use Power Query Formula Language. In your scenario, to calculate the difference between start date and end date with day interval, you can use Duration.Days() and Duration.From() functions like below, you can also plus "1" at the end of the formula. For more information about function, see: Duration functions.

=Duration.Days(Duration.From([End date]-[Start Date]))

In your scenario, if you minus directly, it will return values which are not numbers, so you cann't plus '1'. But you can change this column data type to number then create another column to plus '1'.

Best Regards,
Qiuyun Yu