Group by the last two dates

[gdps_vc]

Greetings to all!

I need help grouping information from a database I have here in power query.

Briefly, I have a table of nominations where the availability of a position is recorded:

Position ID	Date	Availability
VC2021-1023	2024-06-21 16:55:10.0	Ready Now
VC2021-1063	2024-06-20 10:26:09.0	Short Term
VC2021-1077	2024-06-21 11:08:10.0	Long Term
VC2021-1077	2024-06-21 11:16:41.0	Medium Term
VC2021-1077	2024-06-21 11:13:44.0	Medium Term
VC2021-1077	2024-06-17 11:27:36.0	Medium Term
VC2021-1077	2024-06-17 11:29:11.0	Medium Term
VC2021-1077	2024-06-21 11:19:15.0	Short Term
VC2021-1077	2024-06-21 11:06:42.0	Long Term
VC2021-1077	2024-06-17 11:35:50.0	Long Term
VC2021-1077	2024-06-17 11:34:37.0	Medium Term
VC2021-1077	2024-06-16 15:25:49.0	Short Term
VC2021-1102	2024-06-17 12:27:24.0	Medium Term
VC2021-1102	2024-06-21 11:01:17.0	Long Term
VC2021-1102	2024-06-21 10:53:34.0	Medium Term
VC2021-1102	2024-06-17 11:26:46.0	Long Term

In other words, I have that on 21/06/2024 at 16:55:10, an employee was flagged as Ready Now to take over position VC2021-1023.

The problem is: I need to filter for each position and availability only 2 collaborators (the 2 oldest nominations). The expected result for the table above would be something like this:

Position ID	Date	Availability
VC2021-1023	2024-06-21 16:55:10.0	Ready Now
VC2021-1063	2024-06-20 10:26:09.0	Short Term
VC2021-1077	2024-06-21 11:06:42.0	Long Term
VC2021-1077	2024-06-17 11:35:50.0	Long Term
VC2021-1077	2024-06-17 11:27:36.0	Medium Term
VC2021-1077	2024-06-17 11:29:11.0	Medium Term
VC2021-1077	2024-06-21 11:19:15.0	Short Term
VC2021-1077	2024-06-16 15:25:49.0	Short Term
VC2021-1102	2024-06-21 11:01:17.0	Long Term
VC2021-1102	2024-06-17 11:26:46.0	Long Term
VC2021-1102	2024-06-17 12:27:24.0	Medium Term
VC2021-1102	2024-06-21 10:53:34.0	Medium Term

How can I do this within Power Query?

Best Regards,

[WanderingBI · Accepted solution]

Hi,

this will sort the data by [Position ID] and [Date] and only keep the last two dates for each position id:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "nZJNC4JAEIb/yuI5Y2b2K+fatTpUdBEPgVIdTJAi+veNZpAG4nZbFh7ed56ZNI0OSwLCGIF0NIvkbWJwMaFCx9Yywhzkf1sc86faVI8om30xrseAQmByDEnL7M5VfVP7oi77kPeDIGRYfIJW1fU0jZF+BltmXeSXezmR0mxMAIW+ocizduFUwvhHQ6FssECRQZMFvutpy3a69I4xrH2ICKfQMlk2ozch1zeIosY5jW+qT3UiRKAfGeqXAbZa5gpI6rYryt0wKXsB", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Position ID" = _t, Date = _t, Availability = _t]
  ), 
  inputTable = Table.TransformColumnTypes(Source, {{"Position ID", type text}, {"Date", type datetime}, {"Availability", type text}}), 
  inputTableSorted = Table.Sort(inputTable, {{"Position ID", Order.Descending}, {"Date", Order.Descending}}), 
  #"Added Custom" = Table.AddColumn(
    inputTableSorted, 
    "RowsForPositionID", 
    (outerRow) => Table.SelectRows(inputTableSorted, (innerRow) => innerRow[Position ID] = outerRow[Position ID])
  ), 
  #"Added Custom1" = Table.AddColumn(#"Added Custom", "Max2Dates", each List.MaxN([RowsForPositionID][Date], 2)), 
  #"Added Custom2" = Table.AddColumn(#"Added Custom1", "IsInLastTwoDates", each if List.Contains([Max2Dates], [Date]) then true else false), 
  #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([IsInLastTwoDates] = true)), 
  #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows", {"RowsForPositionID", "Max2Dates", "IsInLastTwoDates"}), 
  tableSorted = Table.Sort(#"Removed Columns", {{"Position ID", Order.Descending}, {"Date", Order.Descending}})
in
  tableSorted

Or a more elegant solution with using Table.MaxN:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "nZJNC4JAEIb/yuI5Y2b2K+fatTpUdBEPgVIdTJAi+veNZpAG4nZbFh7ed56ZNI0OSwLCGIF0NIvkbWJwMaFCx9Yywhzkf1sc86faVI8om30xrseAQmByDEnL7M5VfVP7oi77kPeDIGRYfIJW1fU0jZF+BltmXeSXezmR0mxMAIW+ocizduFUwvhHQ6FssECRQZMFvutpy3a69I4xrH2ICKfQMlk2ozch1zeIosY5jW+qT3UiRKAfGeqXAbZa5gpI6rYryt0wKXsB", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Position ID" = _t, Date = _t, Availability = _t]
  ), 
  inputTable = Table.TransformColumnTypes(Source, {{"Position ID", type text}, {"Date", type datetime}, {"Availability", type text}}), 
  inputTableSorted = Table.Sort(inputTable, {{"Position ID", Order.Descending}, {"Date", Order.Descending}}),
    #"Grouped Rows" = Table.Group(inputTableSorted, {"Position ID"}, {{"Filtered", each Table.MaxN(_,"Date",2)}}),
    #"Expanded Filtered" = Table.ExpandTableColumn(#"Grouped Rows", "Filtered", {"Date", "Availability"}, {"Filtered.Date", "Filtered.Availability"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Filtered",{{"Filtered.Date", "Date"}, {"Filtered.Availability", "Availability"}}),
    tableSorted = Table.Sort(#"Renamed Columns", {{"Position ID", Order.Descending}, {"Date", Order.Descending}})
in
    tableSorted

[Alef_Ricardo_]

Para resolver seu problema no Power Query, você pode usar a função `Group By` com algumas personalizações. Aqui estão os passos que você pode seguir:

1. Carregue seus dados no Power Query.
2. Selecione a coluna "Position ID" e depois vá para `Transformar -> Agrupar por`.
3. Na janela "Agrupar por", clique em "Avançado".
4. Em "Coluna de agrupamento", selecione "Position ID" e "Availability".
5. Em "Operações de agrupamento", selecione "Todas as linhas".
6. Clique em "OK". Isso criará uma nova coluna com todas as linhas para cada combinação de "Position ID" e "Availability".
7. Expanda a nova coluna criada e selecione apenas as colunas "Date" e "Availability".
8. Para cada combinação de "Position ID" e "Availability", ordene a coluna "Date" em ordem crescente.
9. Use a função `Table.FirstN` para selecionar as duas primeiras linhas (ou seja, as duas nomeações mais antigas) para cada combinação de "Position ID" e "Availability".

Aqui está um exemplo de como o código M pode parecer:

```text
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Position ID", "Availability"}, {{"AllRows", each _, type table [Position ID=nullable text, Date=nullable datetime, Availability=nullable text]}}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Date"}, {"Date"}),
#"Sorted Rows" = Table.Sort(#"Expanded AllRows",{{"Date", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "Rank", each Table.FirstN(_,2)),
#"Expanded Rank" = Table.ExpandTableColumn(#"Added Custom", "Rank", {"Position ID", "Date", "Availability"}, {"Position ID.1", "Date.1", "Availability.1"})
in
#"Expanded Rank"
```

Por favor, substitua "Table1" pelo nome da sua tabela e ajuste o código conforme necessário. Espero que isso ajude! Se você tiver mais perguntas, fique à vontade para perguntar.