Difference between dates - power query

[PBIfanatic]

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.

[jfcarlier]

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]

*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

[camargos88]

Hi @PBIfanatic ,

Are you using database ? If yes, why don't you create a calculated field on your query ?

Ricardo

[PBIfanatic]

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 🙂

[camargos88]

Hi @PBIfanatic ,

Can you just submit a query instead of change view/procedure ?

Ricardo

[PBIfanatic]

Hi @camargos88 ,

Sorry, do you mean a custom sql?

[camargos88]

@PBIfanatic ,

Yes, I don't know what you are using (view/procedure...) but is it possible to use a hardcode ?

Ricardo

[Anonymous]

Hi @camargos88 ,

At the moment I have a direct query to a table in SQL Server. I would like to make this change in Power query without any change to the table in the db.

I have recreated this in the Adventure works and this is the query generated. WHere do you reckon I need to change?

let
    Source = Sql.Database("******", "AdventureWorks2012"),
    HumanResources_Employee = Source{[Schema="HumanResources",Item="Employee"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(HumanResources_Employee,{"Person.Person", "Production.Document", "Purchasing.PurchaseOrderHeader", "Sales.SalesPerson"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"BirthDate", Order.Ascending}})
in
    #"Sorted Rows"

[camargos88]

Hi @Anonymous ,

After select Direct Query, you click on Advanced options and write your custom SQL.

[Anonymous]

Thanks @camargos88 .

I was thinking if there are other ways of using Power BI functionalities to calculate this. 

[v-yiruan-msft]

Hi @Anonymous ,

Whether the problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.

Best Regards

Rena

[PBIfanatic]

Hi @v-yiruan-msft ,

No, it isnt unfortunately. 

[v-yiruan-msft]

Hi @PBIfanatic ,

You can use the function DATEDIFF in DAX to create one calculated column instead of calculating the difference between dates in Power Query:

Best Regards

Rena