Removing Duplicate Line Items/Documents Based on Most Recent Version

[Anonymous]

I've been wrestling with 'Transform', 'Group By' and 'Advanced Editor' in an attempt to try to remove duplicate CQTitles from the list and the visual in Power BI. Any assistance is appreciated.

[v-deddai1-msft]

Hi @Anonymous ,

Is the date column in your screenshot is the same column as DteofCQ column? It's better that you can show us sample pbix by onedrive for business. So we can help you well.

Best Regards,

Dedmon Dai.

[v-deddai1-msft]

Hi @Anonymous ,

Are you trying to eliminating all duplicates of CQTitles except the most recent in data table or in table visual?

For the former, you can try group by as mentioned by me above.

For the latter, you can create a visual level filter like:

measure = IF(CALCULATE(MAX('Table'[Date]),allexcept('Table','Table'[CQTitle])) = MAX('Table'[Date]),1,0)

 And set it to 1.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Thank you.

The effort returned the following; in the visual you can see the same CQTitle for the individual in 2018 and 2019.

Where such duplicates exist only the MOST Recent should show in the visual, not both.

A refresh then changed the visual table to the following. The duplicates remain, but the dates default to the year '1900'.

Please advise.

[v-deddai1-msft]

Hi @Anonymous ,

You can use group by in query editor:

If you need accurate help, would you please show us sample data and expected output (pbix file) by onedrive for business.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

How would your suggestion be configured to only return the most recent version of all CQTitles; eliminating ALL duplicates of CQTitles except the most recent?

[smpa01]

@Anonymous  please try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTLUN9Q3MjAyUIrVgQgYoQsYwwViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [title = _t, Column1 = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"title", type text}, {"date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"title"}, {{"ad", each _, type table [title=nullable text, Column1=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let x=[ad], y = Table.SelectRows(x, each ([date] = List.Max(x[date]))) in y),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"title", "date"}, {"title", "date"})
in
    #"Expanded Custom"

[Anonymous]

Am I to simply cut and paste this into the 'Advanced Editor' while the 'DteofCQ' is highlighted? Please clarify how this is to be applied.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTLUN9Q3MjAyUIrVgQgYoQsYwwViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [title = _t, Column1 = _t]), #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "date"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"title", type text}, {"date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"title"}, {{"ad", each _, type table [title=nullable text, Column1=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let x=[ad], y = Table.SelectRows(x, each ([date] = List.Max(x[date]))) in y), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"title", "date"}, {"title", "date"}) in #"Expanded Custom"

[smpa01]

@Anonymousno, I wanted you to write a query to show you how can you adapt my query to your scenario. If you want the query to speak exactly to your scenario, please provide sample data here in a format that is copy paste able.

[Anonymous]

I understand, and that's why I'm here.

I'm not trying to have someone else do my work. I am simply unfamiliar with Power BI in this regard. I've built what I've built through trial and error; however, I am completely lost on where and how to start regarding this need.

The need? The following visuals are reconfigured copies of the same visual. All visuals should ONLY show (the table) or tally the (graphs) the most recent document (based on the other query parameters).

Some CQTitles are required to be renewed annually, every two years, every three years. Other documents are a one-time document.

The four documents for which only the most recent version should be viewed/tallied are:
Medical, Fitness Test, Live Fire Refresher, Self-Contained Breathing Apparatus Fit Test.

The columns to use are FullNm, CQTitle, DteofCQ. I hope this helps.

[Anonymous]

I hope this works.

I've provided three members, each with a certification document for the same CQTitle from two different years. The formula/condition should elimate all but the most recent of the documents.

There are 20+ certifications and qualifications. the only for 'actual' qualifications that need to meet the 'no duplicates/only most recent' mandate are below; all other CQTitle should appear, because none of them repeat.

Do Not Repeat/Provide Only Most Recent:

     Medical

     Fitness Test

     Live Fire Refresher

     Self-Contained Breathing Apparatus Fit Test

I tried to use the internal table creator. I kept causing an html error. I hope this works for you. 

[smpa01]

@Anonymous  follow this https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1474631#M614460