Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
niamhks
Regular Visitor

Restrictions with Date/Time Transformations

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. 

 

Capture.JPG

 

Any suggestions or advice would be hugely appreciated! 

 

Thanks 

Niamh 

1 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.

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yulgu-msft
Employee
Employee

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
    )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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! 

 

 

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.

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you @v-yulgu-msft

 

I will give this a go 🙂 

 

Thanks 

Niamh 

Greg_Deckler
Super User
Super User

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

 

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.