Matrix Table - Group first column by date, keeps detailed rows for columns

[Anonymous]

Hello, 

Is it possible to build a matrix table that would read:

Out of data that is like this:

Date	Time start	Time Ends	Session	Speaker	Purpose
6.24.20	12:00 PM	1:00 PM	Love of cats	Miaowton	A group of people who love cats talk about their love
6.24.20	1:00 PM	2:00 PM	cat biology	Dr. Purfect	The details of the organisms of cats
6.25.20	8:00 AM	9:00	tea w/ cats	All	come join us in a cat tea brunch
6.26.20	10:00 AM	12:00 PM	closing catnote	Kat Krispies	Close out the conference

I'm unable to build the matrix table to group the "Date" column data. 

Could someone please help me?

Thank you,

L

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Please check if the following one is what you want:

1. Sort the column Date and Time start by ascending in Power Query Editor

2. Add index column from 1 in Power Query Editor

3. Create a calculated column to get new Date field(if current date is equal to the previous date, then not display. Otherwise, display the corresponding date)

Ndate = 
VAR _predate =
    CALCULATE (
        MAX ( 'Matrix'[Date] ),
        FILTER ( 'Matrix', 'Matrix'[Index] < EARLIER ( 'Matrix'[Index] ) )
    )
RETURN
    IF ( 'Matrix'[Date] = _predate, BLANK (), 'Matrix'[Date] )

Note: The table visual need to sort by Index column, you can hide it by dragging this column...

I just created a sample pbix file for you, you can get it from this link for full detail.

Best Regards

Rena

[Anonymous]

@Anonymous 

Maybe try duplicating the table and deleting all columns except Date and Time Start.

Group by date. With Time Start at max/min/whatever. Doesn't matter. 

Then delete time. 

Then merge with the other table and bring in all the other colums?