Forum Discussion
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 table as this disables the query folding.
Is there any alternate way without importing the table?
thanks.
13 Replies
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.
*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
Hi PBIfanatic ,
Are you using database ? If yes, why don't you create a calculated field on your query ?
Ricardo
Hi camargos88 ,
Thanks for your reply, do you mean creating a column in the database?
The problem is, its a long process to make a change to the db. Hoping Power BI to come to the rescue 🙂
