Get value of Last Row within an unfixed range

[ewu]

To the experts: 

How could I go from the 1sts column raw data to get results of the 2nd and 3rd column? Thanks so much.

Eleanor

[Greg_Deckler]

@ewu PBIX is attached below signature. You first want to add an Index to your table in Power Query Editor.

Group = 
    VAR __Values = { "A", "B", "C" }
    VAR __Current = [Raw Data]
    VAR __Result = 
        IF( 
            __Current IN __Values,
            __Current,
                VAR __Index = [Index]
                VAR __PrevIndex = MAXX( FILTER( 'Table', [Index] < __Index && [Raw Data] IN __Values ), [Index] )
                VAR __Result = MAXX( FILTER( 'Table', [Index] = __PrevIndex ), [Raw Data] )
            RETURN
                __Result
        )
RETURN
    __Result

Latest Date = 
    VAR __Group = [Group]
    VAR __Values = { "A", "B", "C" }
    VAR __Table = 
        ADDCOLUMNS(
            FILTER( 'Table', [Group] = __Group && NOT( [Raw Data] IN __Values ) ),
            "__Date", DATEVALUE( [Raw Data] )
        )
    VAR __Result = MAXX( __Table, [__Date] )
RETURN
    __Result

[ewu]

Crazy! Thanks very much!

[SachinNandanwar]

Output =
CALCULATE (
    MAX ( 'Table'[Date] ),
    ALLEXCEPT (
        'Table',
        'Table'[Type]
    )
)

There is one less row for 'A' in my data

[ewu]

Even crazier! 🙏🙏 

[SachinNandanwar · Accepted solution]

If it helped then kindly marked it resolved.