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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Pandadev
Post Prodigy
Post Prodigy

Index order of duplicates based on id

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

1 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.

RawRawGroupedGrouped

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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.

RawRawGroupedGrouped

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks , that worked perfectly

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.