Index order of duplicates based on id

[Pandadev]

I am trying to find a way to filter duplicates on month value

If month value is 1 , keep 1st duplicated id

If month value is 2 , keep 2nd duplicated id

If month value is 3 , keep 3rd duplicated id

The data has duplicated id's for each period

ID     Month    Dep      Arr

305   1             11:50    13:25

303   1             08:05     09:40

301   1             07:05     08:40

305   2             11:25    13:00

303   2             09:05    10:40

301   2             06:05     07:40

305   3             10:20    11:45

303   3             06:05    07:40

301   3             08:15    09:55

So if Month equal 3 then keep only

305   3             10:20    11:45

303   3             06:05    07:40

301   3             08:15    09:55

I was hoping to get the Month Number by Indexing duplicates , so first duplicate would be 1 , 2nd 2 and 3rd 3. and then say if month = 3 then only keep indexed duplicates 3

[mahoneypat]

You can do that by adding a Filter to your Month column (e.g., filter it to 3), and then replacing the 3 in the Formula Bar with the red text below.  This will dynamically filter it to the current month.  FYI that you'll need to adapt it if you have more than one year of data.

= Table.SelectRows(#"Changed Type", each ([Month] = Date.Month(Date.From(DateTime.LocalNow()))))

Pat

[Pandadev]

How do I get an index for duplicates , so I can add either a 1 , 2 or 3 in the month column , so firts duplicate is 1 , 2nd duplicate is 2 and 3rd duplicate is 3

ID     Month    Dep      Arr

305   1             11:50    13:25

303   1             08:05     09:40

301   1             07:05     08:40

305   2             11:25    13:00

303   2             09:05    10:40

301   2             06:05     07:40

305   3             10:20    11:45

303   3             06:05    07:40

301   3             08:15    09:55

[v-shex-msft · Accepted solution]

Hi @Pandadev,

Do you mean the month field values are not really included in your tables, right? If that is the case, you can try to group record by ID and nested 'add index' function into group function at the query editor side.

RawGrouped

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY87DoAwDEOvgjJ3cNoG2mwcoBI74v7XgPKpwkditPXkJ88zBQg5YlaBAt1YalIvNUyFFleRsJVJIY3IGi/8IHgrB0ukJ3FqvBgN8NJkO8L48vQWGT49UG/usEa5MeF3hfcLbB9L21hW", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Dep = _t, Arr = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Dep", type time}, {"Arr", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Content", each Table.AddIndexColumn(_,"Month",1,1), type table [ID=nullable number, Dep=nullable time, Arr=nullable time]}}),
    #"Expanded Content" = Table.ExpandTableColumn(#"Grouped Rows", "Content", {"Dep", "Arr","Month"}, {"Dep", "Arr","Month"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Content",{{"Month", Order.Ascending}})
in
    #"Sorted Rows"

Regards,

Xiaoxin Sheng

[Syndicate_Admin]

Thanks , that worked perfectly