Forum Discussion

huguest's avatar
huguest
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Report the latest or blank date value

Hello,   I have top level issues that can have one or more items underneath it.  I am trying to report the completion date for each Issue that represents the completion of the latest item for that ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    A Power Query solution:

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue", Int64.Type}, {"Item", Int64.Type}, {"Completion Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Issue"}, {{"Completion Date", each Table.Max(_,"Item")[Completion Date], Value.Type(#"Changed Type")}})
in
    #"Grouped Rows"
huguest's avatar
huguest
Icon for Advocate II rankAdvocate II
8 years ago

Zubair_Muhammad thank you for your help.  I tried it but got an error while trying to create the Table "A table of multiple values was supplied where a single value was expected"....

 

I tied another approach where I copied the date column and replaced the balnk dates with 2999-12-31.  That allows me to get those using "latest".

 

Hugues.

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
8 years ago

A Power Query solution:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue", Int64.Type}, {"Item", Int64.Type}, {"Completion Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Issue"}, {{"Completion Date", each Table.Max(_,"Item")[Completion Date], Value.Type(#"Changed Type")}})
in
    #"Grouped Rows"