Restrictions with Date/Time Transformations

[niamhks]

Hi Everyone, 

An app developer of ours has created an app to convert reports into pdf's and refresh and send out reports to a mailing list automatically. therefore we are trying to make all the reports that are going to be sent. 

The first roadblock we hit was for the reports to refresh automatically we have to produce them using direct query. I am now struggling with a transformation I am restricted to do. 

I am trying to extract the minutes or duration between these two columns. I have tried everything I can think of and i'm caught out by the restrictions sooner or later. 

Any suggestions or advice would be hugely appreciated! 

Thanks 

Niamh 

[v-yulgu-msft]

Hi @niamhks,

Duration() is not supported in Direct Query. To achieve the same result, you could create calculated columns using DAX in Report View mode.

second =
DATEDIFF ( Test4[search_start_date], Test4[search_complete_date], SECOND )

H:m:s =
RIGHT ( "0" & INT ( Test4[second] / 3600 ), 2 )
    & ":"
    & RIGHT (
        "0"
            & INT ( ( Test4[second] - INT ( Test4[second] / 3600 ) * 3600 ) / 60 ),
        2
    )
    & ":"
    & RIGHT (
        "0"
            & INT (
                Test4[second]
                    - INT ( Test4[second] / 3600 )
                        * 3600
                    - INT ( ( Test4[second] - INT ( Test4[second] / 3600 ) * 3600 ) / 60 )
                        * 60
            ),
        2
    )

Best regards,

Yuliana Gu

[niamhks]

Thank you for your suggestion. 

I am wanting to split the column in the query as want to then use that column in the report as opposed to creating this column in the report. 

My understanding is that it isn't possible just thought i'd throw the question out there to see if anyone knew of a way! 

[v-yulgu-msft · Accepted solution]

Hi @niamhks,

Unfortunately, it is not possible to extract only date or time part from a datetime field in Power Query with Direct Query connection. This is the limiattion of Direct Query. But we are able to extract Hour/Minute/Second from DateTime.

As a workaround, a new created calculated column in data view can also be used in report page. It works the same as custom column created in Power Query.

Best regards,

Yuliana Gu

[niamhks]

Thank you @v-yulgu-msft. 

I will give this a go 🙂 

Thanks 

Niamh 

[Greg_Deckler]

So, are you saying that DATEDIFF doesn't work? Have you tried a measure like:

Measure = ([search_end_date] - [search_start_date]) * 1.