Simple table with group by header

[mcoll1008]

Hi All,

New PowerBI user who is trying to build a straightforward tabular report for my business users. I would like to use DirectQuery so that the data is as current as it can be. I have a SQL query which pulls the data in from a MS SQL database. There is a column (Due Date) in the data set which is a datetime field formatted thusly - YYYY-MM-DD HH:MM:SS.SSSSSSS (e.g. 2022-01-02 17:00:00.0000000). I am trying to create a tabular report that groups by the Due Date column (but only on the date, not the time) so that all of the rows where the date is say the 1st Jan are together followed by all of the rows where the date is the 2nd Jan are together.

Below is a screenshot of an Excel sample report provided the business which was provided which shows how they want the data grouped:

Is this possible to do in Power BI and if so, how do I do it?

I was playing around for several hours yesterday trying to get an output simular to what they provided but I kept running into problems. The simple table does not allow me to group data and the Matrix table tries to summarize all of the rows and even when I was able to group by the Due Date Column, Power BI would complain that the resulting step renders the dataset invalid for DirectQuery and that I would have to switch to Import mode which I tried to do but then the Desktop application froze and I got frustrated and signed off for the day.

Thanks in advance!

Martin

[amitchandak]

@mcoll1008 ,Based on what I got

there are limited number of functions you can use to create new columns in Power query and DAX in direct query mode

Check if one of these work

Power query

Date = DateTime.Date([Datetime])

or DAX

 date= datevalue([Datetime])

I have not tested as of now, need to start in env.

[mcoll1008]

Thank you so much @amitchandak  for your quick reply. So with these functions if they work and create a virtual column that simply returns the date from the Due Date column, is it then possible to group rows into sections by that new virtual column like how the Excel screenshot shows in my original post?