Summarize Columns bringing only the latest value per school

[maldonna]

I was given an export of a form where users do not always fill in every field. I need to create a table from the export that groups by school and pulls the latest entry for each column ignoring blanks.  I have tried multiple queries. 

This is the query I have:

NewTable =

ADDCOLUMNS(

    SUMMARIZE(

        'Sample',

        'Sample'[School]

    ),

    "Colors", CALCULATE(FIRSTNONBLANK('Sample'[Colors], 'Sample'[Colors])),

    "Food", CALCULATE(FIRSTNONBLANK('Sample'[Food], 'Sample'[Food])),

    "Travel", CALCULATE(FIRSTNONBLANK('Sample'[travel], 'Sample'[travel]))

    // ... add more columns as needed

)

The expectation is that I would get Ellis, green banana, japan and Porter, pink, pineap, italy.  /

As you can see that is not the result.  Any help would be appreciated.

[Anonymous · Accepted solution]

Hi, @maldonna 

I am glad to help you.   

According to your description, you want to know how to summarize Columns bringing only the latest value per school? 

If I understand you correctly, then you can refer to my solution.  

Please consider using Index for sorting to get the latest value. 

You can add a new column Index in Power Query Editor as shown below: 

Then New Table, the specific formula is as follows: 

NewTable = 
SUMMARIZE (
    'Sample',
    'Sample'[School],
    "Colors",
        CALCULATE (
            MAXX (
                TOPN (
                    1,
                    FILTER ( 'Sample', 'Sample'[Colors] <> BLANK () ),
                    'Sample'[Index], DESC
                ),
                'Sample'[Colors]
            )
        ),
    "Food",
        CALCULATE (
            MAXX (
                TOPN (
                    1,
                    FILTER ( 'Sample', 'Sample'[Food] <> BLANK () ),
                    'Sample'[Index], DESC
                ),
                'Sample'[Food]
            )
        ),
    "Travel",
        CALCULATE (
            MAXX (
                TOPN (
                    1,
                    FILTER ( 'Sample', 'Sample'[Travel] <> BLANK () ),
                    'Sample'[Index], DESC
                ),
                'Sample'[Travel]
            )
        )
)

Finally you can realize the results you want: 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Irwan · Accepted solution]

hello @maldonna 

please check if this accomodate your need.

Summarize =
SUMMARIZECOLUMNS(
    'Table'[School],
    "Last Color",
        var _LastIndexColor = CALCULATE(MAX('Table'[Index]),not 'Table'[Colors]=BLANK())
        Return
    CALCULATE(MAX('Table'[Colors]),'Table'[Index]=_LastIndexColor),
    "Last Food",
        var _LastIndexFood = CALCULATE(MAX('Table'[Index]),not 'Table'[Food]=BLANK())
        Return
    CALCULATE(MAX('Table'[Food]),'Table'[Index]=_LastIndexFood),
    "Last Travel",
        var _LastIndexTravel = CALCULATE(MAX('Table'[Index]),not 'Table'[Travel]=BLANK())
        Return
    CALCULATE(MAX('Table'[Travel]),'Table'[Index]=_LastIndexTravel)
)

As your requirement, you need to get the latest value so i created 'Index' column from Power Query for this example.

Otherwise, you need date/time to identify the latest value in your real tabledata.

Hope this will help you.

Thank you.

[Anonymous · Accepted solution]

Hi, @maldonna 

I am glad to help you.   

According to your description, you want to know how to summarize Columns bringing only the latest value per school? 

If I understand you correctly, then you can refer to my solution.  

Please consider using Index for sorting to get the latest value. 

You can add a new column Index in Power Query Editor as shown below: 

Then New Table, the specific formula is as follows: 

NewTable = 
SUMMARIZE (
    'Sample',
    'Sample'[School],
    "Colors",
        CALCULATE (
            MAXX (
                TOPN (
                    1,
                    FILTER ( 'Sample', 'Sample'[Colors] <> BLANK () ),
                    'Sample'[Index], DESC
                ),
                'Sample'[Colors]
            )
        ),
    "Food",
        CALCULATE (
            MAXX (
                TOPN (
                    1,
                    FILTER ( 'Sample', 'Sample'[Food] <> BLANK () ),
                    'Sample'[Index], DESC
                ),
                'Sample'[Food]
            )
        ),
    "Travel",
        CALCULATE (
            MAXX (
                TOPN (
                    1,
                    FILTER ( 'Sample', 'Sample'[Travel] <> BLANK () ),
                    'Sample'[Index], DESC
                ),
                'Sample'[Travel]
            )
        )
)

Finally you can realize the results you want: 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.