Forum Discussion

adegrno's avatar
adegrno
Advocate II
6 years ago

days between two integer date fields in Direct query mode

Hello all,

 

I have 2 integer date fields (YYYYMMDD) in my fact table with several million records, and I work in direct query mode.

 

I have trouble doing the nescessary calculations (f.eks. number of days between the two dates) because PBI will not allow me to do the nescessary field transformations or adding of columns.

 

Importing the table is not an option, it will take too much time. Any suggestions or workarounds for this issue?

 

Thanks,

 

Need Help

4 Replies

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi adegrno,

    If you are using SQL server direct query, you coiuld try to achieve this in M code like below

    = Table.AddColumn(dbo_test, "Custom", each Duration.Days(Duration.From(Date.FromText(Text.From([ET]))-Date.FromText(Text.From([ST])))))

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • adegrno's avatar
      adegrno
      Advocate II
      6 years ago

      Thanks for yr reply Zoe, is there something that should be written in front of the "="?

       

      = Table.AddColumn(dbo_test, "Custom", each Duration.Days(Duration.From(Date.FromText(Text.From([ET]))-Date.FromText(Text.From([ST])))))

      the "dbo_test" I replace with the table name in which my integer date columns are. When I write the table name it will answer the table name is not correctly spelled. I am 100% sure it is.

       

      the ST and ET I have no problems with.

       

      I am connected to a SQL server 

       

      Regards,

       

      • adegrno's avatar
        adegrno
        Advocate II
        6 years ago

        Hello again

         

        ok so I checked the syntax and it works just fine as long as I am in query editor mode.

         

        When I wish to close and use the results in the model PBI will state that this step contains a query that is not supported in direct query mode.

         

        So, I see the Custom column, the values are correct in the column (meaning your M does the trick) but Power bi will not let me use it.

         

        Any suggestions?

         

        Regards,

        Albert