Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

Hello, 

 

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

 

 

 

Out of data that is like this:

 

DateTime startTime EndsSessionSpeakerPurpose
6.24.2012:00 PM1:00 PMLove of catsMiaowtonA group of people who love cats talk about their love
6.24.201:00 PM2:00 PMcat biologyDr. PurfectThe details of the organisms of cats
6.25.208:00 AM9:00tea w/ catsAllcome join us in a cat tea brunch
6.26.2010:00 AM12:00 PMclosing catnoteKat KrispiesClose 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

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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