Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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,
Solved! Go to 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
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
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.