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. num...
Need Help
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

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi adegrno.

    I forget this is direct query mode, you could try to achieve this in sql query like below

    select name, amount,  convert(datetime,convert(varchar(20),st))   as st, convert(datetime,convert(varchar(20),et))   as et  from test

    You could convert this in sql, then use converted data in powerbi.

    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.