How to return cell header with latest date

[Sian1]

Hi, i have 4 columns with various date on it, how could i write a formula to return the header named based on the latest date

A	B	C	D	Return Value
	1/20/2020	1/20/2020	1/23/2020	D
7/11/2019	5/28/2019	8/6/2020	8/10/2020	D
	7/5/2021	6/23/2021	6/23/2021	B
7/5/2021	1/14/2021	1/14/2021	1/15/2021	A
	8/10/2020	1/3/2020	1/6/2020	B
	10/10/2019	6/23/2021	10/27/2020	C

 

[JGordon11]

No you would have to modify it. Something like

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"A", type date}, {"B", type date}, {"C", type date}, {"D", type date}}),
    tbl = Table.SelectColumns(ChangeType, List.RemoveItems(Table.ColumnNames(ChangeType), {"ID"})),
    AddCol = Table.AddColumn(tbl, "Latest Date", each Table.FromColumns({Table.ColumnNames(tbl),Record.ToList(_)})),
    TransformCol = Table.TransformColumns(AddCol, {"Latest Date", each Text.Combine(Table.SelectRows(_, (x)=> x[Column2] = List.Max(_[Column2]))[Column1],", ")}),
    Result = Table.FromColumns({ChangeType[ID]} & Table.ToColumns(TransformCol), {"ID"} & Table.ColumnNames(TransformCol))
in
    Result

 

[Sian1]

thanks it works, however if all items blank, it still comeback with details. Do you know how to solve it ?

 

[JGordon11]

maybe

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"A", type date}, {"B", type date}, {"C", type date}, {"D", type date}}),
    tbl = Table.SelectColumns(ChangeType, List.RemoveItems(Table.ColumnNames(ChangeType), {"ID"})),
    AddCol = Table.AddColumn(tbl, "Latest Date", each Table.FromColumns({Table.ColumnNames(tbl),Record.ToList(_)})),
    TransformCol = Table.TransformColumns(AddCol, {"Latest Date", each 
        if List.Count(List.RemoveNulls(_[Column2]))>0 then 
            Text.Combine(Table.SelectRows(_, (x)=> x[Column2] = List.Max(_[Column2]))[Column1],", ") 
        else 
            null})
in
    TransformCol

 

[Sian1]

i added the "else null" statement it didn't come out correctly. the reasons probably because i have a lot more fields before these 4 particulars field. I created a table in within the table here is my query. how can I modify item 11?

 

[Sian1]

oh i got it

thanks so much!!

 

[jdellasala]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"A", type date}, {"B", type date}, {"C", type date}, {"D", type date}}),
    AddCol = Table.AddColumn(ChangeType, "Latest Date", each Table.FromColumns({Table.ColumnNames(ChangeType),Record.ToList(_)})),
    TransformCol = Table.TransformColumns(AddCol, {"Latest Date", each Text.Combine(Table.SelectRows(_, (x)=> x[Column2] = List.Max(_[Column2]))[Column1],", ")})
in
    TransformCol

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	A	B	C	D		A	B	C	D	Latest Date
2		1/20/2020	1/20/2020	1/23/2020			1/20/2020	1/20/2020	1/23/2020	D
3	7/11/2019	5/28/2019	8/6/2020	8/10/2020		7/11/2019	5/28/2019	8/6/2020	8/10/2020	D
4		7/5/2021	6/23/2021	6/23/2021			7/5/2021	6/23/2021	6/23/2021	B
5	7/5/2021	1/14/2021	1/14/2021	1/15/2021		7/5/2021	1/14/2021	1/14/2021	1/15/2021	A
6		8/10/2020	1/3/2020	1/6/2020			8/10/2020	1/3/2020	1/6/2020	B
7	6/23/2021	10/10/2019	6/23/2021	10/27/2020		6/23/2021	10/10/2019	6/23/2021	10/27/2020	A, C
8
Sheet1

Very nice. I especially like that there are no SPACES in your step names. Keeps the code so much cleaner!

 

[GraH]

Alternative (not necessarily better, maybe easier to understand without any "(x)=>" function construction).

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    Cols = List.Buffer(Table.ColumnNames(Source)),
    Set_as_dates = Table.TransformColumnTypes(Source,List.Transform(Cols, each {_, type date})),
    Find_header_for_max_date = Table.AddColumn(Set_as_dates, "Max Header", each 
    let 
        RecordList = Record.ToList(_),
        Max = List.Max(RecordList),
        PosOfMax = List.PositionOf(RecordList, Max),
        Header = if Max = null then null else List.Range(Cols, PosOfMax,1){0}
    in 
        Header)
in
    Find_header_for_max_date