Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
gdps_vc
Helper I
Helper I

Group by the last two dates

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 IDDateAvailability
VC2021-10232024-06-21 16:55:10.0Ready Now
VC2021-10632024-06-20 10:26:09.0Short Term
VC2021-10772024-06-21 11:08:10.0Long Term
VC2021-10772024-06-21 11:16:41.0Medium Term
VC2021-10772024-06-21 11:13:44.0Medium Term
VC2021-10772024-06-17 11:27:36.0Medium Term
VC2021-10772024-06-17 11:29:11.0Medium Term
VC2021-10772024-06-21 11:19:15.0Short Term
VC2021-10772024-06-21 11:06:42.0Long Term
VC2021-10772024-06-17 11:35:50.0Long Term
VC2021-10772024-06-17 11:34:37.0Medium Term
VC2021-10772024-06-16 15:25:49.0Short Term
VC2021-11022024-06-17 12:27:24.0Medium Term
VC2021-11022024-06-21 11:01:17.0Long Term
VC2021-11022024-06-21 10:53:34.0Medium Term
VC2021-11022024-06-17 11:26:46.0Long 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 IDDateAvailability
VC2021-10232024-06-21 16:55:10.0Ready Now
VC2021-10632024-06-20 10:26:09.0Short Term
VC2021-10772024-06-21 11:06:42.0Long Term
VC2021-10772024-06-17 11:35:50.0Long Term
VC2021-10772024-06-17 11:27:36.0Medium Term
VC2021-10772024-06-17 11:29:11.0Medium Term
VC2021-10772024-06-21 11:19:15.0Short Term
VC2021-10772024-06-16 15:25:49.0Short Term
VC2021-11022024-06-21 11:01:17.0Long Term
VC2021-11022024-06-17 11:26:46.0Long Term
VC2021-11022024-06-17 12:27:24.0Medium Term
VC2021-11022024-06-21 10:53:34.0Medium Term

 

How can I do this within Power Query?

 

Best Regards,

 

1 ACCEPTED SOLUTION
WanderingBI
Resolver III
Resolver III

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

 

WanderingBI_0-1719365529208.png

 

View solution in original post

2 REPLIES 2
WanderingBI
Resolver III
Resolver III

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

 

WanderingBI_0-1719365529208.png

 

Alef_Ricardo_
Resolver II
Resolver II

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Kudoed Authors