Just a quick tip that you might not be aware of in the Power Query Editor.
If you select two columns in the view and on the Add Column tab, select the Date button – you can select to Subtract Days
This will give you the number of days between the dates in the selected columns
Use the formula bar to rename the new column by modifying the step
Hope this can help you too –
Will this work if the column is a datetime field?
If both field is datetime – yes it should work – other wise you can subtract time in the same menu
How about if I only need “business” days? Any shortcuts?
With business days – do you mean mon-fri or do you wish to take other holidays into account?
Hi – interesting – is there a reason you wouldn’t use datediff function? I’m asking as when i use datediff against redshift database it gives me some rows correct and others a big negative number. If i use your method it does seem to correctly calculate. i wonder if there’s an issue with datediff. thanks!
Hi – There is no datediff function in Power Query 🙂
Hi Erik, I have the same Question as Patrick. What i’d only like to see the Workdays (No holidays, no weekenddays) between the two columns?
Hi Steve
Do you have a dates table that has columns containing weekday number and an indication of the date is a holiday ?
If you have then you can add a calculated column that contains a formula something like this Table.Rowcount(table.selectrows(datetable, each
[date] >= [StartDate] and [date] <= [enddate] and [weekday] <=5 and [Holiday] = false))
Answering via phone so no M intellisense 🙂
Best
Erik