Forum Discussion

PBIfanatic's avatar
PBIfanatic
Helper V
6 years ago

Difference between dates - power query

Hi,   How do I calculate the difference between the dates in power query without loosing query folding? I tried using Duration.Days function, but it comes with an error saying I need to import the...
Need Help
jfcarlier's avatar
jfcarlier
New Member
3 years ago

My method is a bit convoluted, but it works.

 

1. Duplicate both column dates.

2. Change these new columns' types to the Integer type (whole number?).

3. Create a new subtraction column from these new Integer "proxy date" columns to get the number of days between 2 dates

 

Bonus. You can even use this Integer column to add/subtract to any transformed date and then change back the integer result to a date type.

BA_Pete's avatar
BA_Pete
Super User
3 years ago

 

*For any future readers*

 

As jfcarlier suggests, converting to numbers first will work, but (generally) not integers.

 

The following works for days difference between DateTime values folding against SQL Server:

Table.AddColumn(
    previousStepName,
    "columnName",
    each Number.From(Date.From([endDate])) - Number.From(Date.From([startDate]))
)

If you already have Date values, not DateTime values, you can remove the Date.From functions accordingly.

 

To have the output correctly typed in a single step:

Table.TransformColumnTypes(
    Table.AddColumn(
        previousStepName,
        "columnName",
        each Number.From(Date.From([endDate])) - Number.From(Date.From([startDate]))
    ), 
    {{"columnName", type number}}
)

 

Pete