Filtrar duplicados

[authsyxm]

Hola,

estoy intentando buscar en una columna que tiene valores duplicados, a su vez la consulta tiene un índice, el objetivo es que si se encuentra una fila duplicada, se devuelva la siguiente posición (indice-número de fila) en la que se ubica ese valor duplicado, ejemplo:

en la fila 71 hay un valor que también está en la fila 93, el resultado debería ser que en la fila 71 en otra columna documente 93, así sucesivamente... no logro configurar el código, siempre me da como resultado un error por evaluación cíclica incorrecta... ayuda!!!

[v-pnaroju-msft]

Hi Authsyxm,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

Thank you @Omid_Motamedise for the response.

Hi @authsyxm,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

Based on the query posted, please find the M code below, which can be used directly in the Advanced Editor. Additionally, kindly refer to the sample input dataset and the expected output provided below:

Input dataset:

Output:

let
Source = Table.FromRecords({
[Index=1, Value="A"],
[Index=2, Value="B"],
[Index=3, Value="A"],
[Index=4, Value="C"],
[Index=5, Value="B"],
[Index=6, Value="D"],
[Index=7, Value="A"]
}),

DuplicateTable = Source,

MergedTable = Table.NestedJoin(Source, "Value", DuplicateTable, "Value", "DuplicateTable", JoinKind.Inner),

ExpandedTable = Table.ExpandTableColumn(MergedTable, "DuplicateTable", {"Index"},{"DuplicatedTable.Index"}),

FilteredTable = Table.AddColumn(ExpandedTable, "Next Occurrence Index", each if [Index] < [DuplicatedTable.Index] then [DuplicatedTable.Index] else null),

GroupedTable = Table.Group(FilteredTable, {"Index"},{{"Next Occurrence Index", each List.Min(List.RemoveNulls([Next Occurrence Index])), type nullable number}}),

FinalTable = Table.ReplaceValue(GroupedTable, null, "-", Replacer.ReplaceValue, {"Next Occurrence Index"})

in
FinalTable

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[Omid_Motamedise]

You can merge the table with itselfe based on the column you search for duplicate values.