cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate III
Advocate III

Power bi data - removing duplicates.

Hello All, Need ideas for removing duplicate of ticket numbers from a set of data. 

Every time there is a change request the draft number creates assigned to the same ticket. 

However, I need only the latest tickets based on the latest draft number of the ticket for the same market. 

Below is a sample of data, any ideas are helpful. highlighted in different colors to show they

 

Ticket NumberDraftMarketJob TypeStatusDate
CH-2019-04304SpainStatic PDF/PPTApproved02-01-2020
CH-2019-04305SpainStatic PDF/PPTApproved07-02-2020
CH-2019-06614FranceLocalisationApproved02-01-2020
CH-2019-09032Hong KongLocalisationApproved02-01-2020
CH-2019-09151SlovakiaLocalisationApproved02-01-2020
CH-2019-09161SlovakiaLocalisationApproved02-01-2020
CH-2019-09241BelgiumInteractive PPT/PDFApproved02-01-2020
CH-2019-09251BelgiumInteractive PPT/PDFApproved02-01-2020
CH-2019-09291FranceStatic PDF/PPTApproved02-01-2020
CH-2020-09411IndiaHTML Conversion (PDF)Approved13-01-2020
CH-2020-09421IndiaHTML Conversion (PDF)Approved13-01-2020
CH-2020-09431IndiaHTML Conversion (PDF)Approved13-01-2020
CH-2020-09441IndiaHTML Conversion (PDF)Approved13-01-2020
CH-2020-09451IndiaHTML Conversion (PDF)Approved13-01-2020
CH-2019-09212GermanyInteractive PPT/PDFApproved07-01-2020
CH-2019-09371PolandInteractive PPT/PDFApproved07-01-2020
CH-2020-09461FranceInteractive PPT/PDFApproved09-01-2020
CH-2020-09462FranceInteractive PPT/PDFApproved16-01-2020
CH-2020-09463FranceInteractive PPT/PDFApproved24-01-2020
CH-2020-09464FranceInteractive PPT/PDFApproved03-02-2020
CH-2020-09465FranceInteractive PPT/PDFApproved18-02-2020
CH-2020-09466FranceInteractive PPT/PDFApproved12-03-2020
CH-2019-08572USALocalisationApproved03-02-2020
CH-2019-08573USALocalisationApproved18-02-2020
CH-2019-08632Great BritainLocalisationApproved06-01-2020
CH-2020-09471FinlandInteractive PPT/PDFApproved08-01-2020
CH-2019-08982PortugalLocalisationStaged 
CH-2019-08983PortugalLocalisationStaged 
CH-2019-08984PortugalLocalisationStaged 
CH-2019-08985PortugalLocalisationApproved24-01-2020
CH-2020-09481CanadaStatic PDF/PPTApproved29-01-2020
CH-2020-09491MoldovaLocalisationStaged 
CH-2020-09492MoldovaLocalisationApproved26-03-2020
CH-2020-09501MoldovaLocalisationApproved10-01-2020
CH-2019-08942LatviaLocalisationStaged 
CH-2019-08943LatviaLocalisationStaged 
CH-2019-08944LatviaLocalisationApproved20-01-2020
CH-2020-09511LithuaniaLocalisationStaged 
CH-2020-09512LithuaniaLocalisationStaged 
CH-2020-09513LithuaniaLocalisationApproved07-02-2020
CH-2020-09522IrelandStatic PDF/PPTApproved07-01-2020
CH-2020-09531EstoniaLocalisationStaged 
CH-2020-09532EstoniaLocalisationStaged 
CH-2020-09533EstoniaLocalisationApproved23-01-2020
CH-2019-06995SpainLocalisationApproved14-01-2020
CH-2019-06996SpainLocalisationStaged 
CH-2019-06997SpainLocalisationStaged 
CH-2019-06998SpainLocalisationStaged 
CH-2019-06999SpainLocalisationApproved28-04-2020
CH-2019-09311CanadaStatic PDF/PPTApproved08-01-2020
CH-2019-09321CanadaStatic PDF/PPTApproved08-01-2020
CH-2019-09331CanadaStatic PDF/PPTApproved08-01-2020
CH-2019-09341CanadaStatic PDF/PPTApproved08-01-2020
CH-2019-09022CanadaInteractive PPT/PDFStaged 
CH-2019-09023CanadaInteractive PPT/PDFStaged 
CH-2019-09024CanadaInteractive PPT/PDFApproved25-02-2020
CH-2020-09541CanadaStatic PDF/PPTDeleted 
CH-2020-09551CanadaStatic PDF/PPTStaged 
CH-2020-09552CanadaStatic PDF/PPTApproved29-01-2020
CH-2020-09561IrelandInteractive PPT/PDFApproved10-01-2020
CH-2020-09571IrelandVideoApproved09-01-2020
CH-2020-09581Ireland   
CH-2020-09591IrelandVideoApproved09-01-2020
CH-2019-07413LatviaLocalisationApproved10-01-2020
CH-2020-09601FranceVideoApproved14-01-2020
CH-2020-09611FranceVideoApproved14-01-2020
CH-2020-09621Great BritainInteractive PPT/PDFStaged 
CH-2020-09622Great BritainInteractive PPT/PDFApproved23-01-2020
CH-2019-08061DenmarkStatic PDF/PPTApproved10-01-2020
CH-2019-08062DenmarkStatic PDF/PPTApproved10-02-2020
CH-2019-00226RomaniaLocalisationApproved04-02-2020
CH-2020-09631DenmarkStatic PDF/PPTApproved10-01-2020
CH-2020-09632DenmarkStatic PDF/PPTApproved10-02-2020
CH-2020-09641Benin   
CH-2020-09651FranceStatic PDF/PPTApproved03-02-2020
CH-2020-09661FranceVideoApproved14-01-2020
CH-2020-09671FranceVideoApproved14-01-2020
CH-2020-09681NigeriaVideoApproved14-01-2020
CH-2019-09242BelgiumInteractive PPT/PDFApproved10-01-2020
CH-2019-09252BelgiumInteractive PPT/PDFApproved10-01-2020
CH-2020-09691Great BritainLocalisationStaged 
CH-2020-09692Great BritainLocalisationApproved30-01-2020
CH-2020-09693IrelandLocalisationApproved05-02-2020
CH-2019-09033Hong KongLocalisationStaged 
CH-2020-09701FranceVideoApproved10-01-2020
CH-2020-09711FranceVideoApproved10-01-2020
CH-2020-09721PhilippinesLocalisationStaged 
CH-2020-09722PhilippinesLocalisationStaged 
CH-2020-09723PhilippinesLocalisationStaged 
CH-2020-09731FranceVideoApproved10-01-2020
CH-2020-09741FranceVideoApproved10-01-2020
CH-2020-09751FranceVideoApproved10-01-2020
CH-2020-09761FranceVideoApproved10-01-2020
CH-2020-09771IndiaHTML Conversion (PDF)Approved13-01-2020
CH-2020-09781IndiaHTML Conversion (PDF)Approved13-01-2020
CH-2020-09791BeninStatic PDF/PPTExpired20-01-2020
CH-2020-09801CameroonStatic PDF/PPTExpired20-01-2020
CH-2020-09811CongoStatic PDF/PPTExpired20-01-2020
CH-2020-09821GabonStatic PDF/PPTExpired20-01-2020
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Power bi data - removing duplicates.

@bushpalaneelima ,

 

Glad to hear that! I have moved the case from issues forum to desktop forum. Could you please mark the appropriate reply as a solution? Thank you very much.

 

Regards,

Jimmy Tao

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Power bi data - removing duplicates.

@bushpalaneelima ,

 

In query editor, click duplicate the table, in the original table, click transform-> group by as below:

Capture.PNG 

Then merge the original table with the duplicated table using left outer join as below:

2.PNG 

 

Regards,

Jimmy Tao

Highlighted
Advocate III
Advocate III

Re: Power bi data - removing duplicates.

Thank you for your response that is helpful. the thing is every time there is a change made to the ticket it created new draft number. but for reporting, I need only the latest draft ticket to be considered as the data is updated and saved in the latest draft. by doing what you have proposed I got only one ticket per draft. I need every ticket number with its latest draft number only. From extract like below. I need only the lastest out put, which in this case bill ticket CH-2020-0953 and draft 3 and the rest of the row data to be flowing into data view post transformation. Ticket Number Draft Market Job Type Status Date CH-2020-0953 1 Estonia Localisation Staged CH-2020-0953 2 Estonia Localisation Staged CH-2020-0953 3 Estonia Localisation Approved 23-01-2020 As you could see I could not consider date as not all rows will have date. Is there anything else that could be done.
Highlighted
Advocate III
Advocate III

Re: Power bi data - removing duplicates.

idea provided by 

 

Highlighted
Community Support
Community Support

Re: Power bi data - removing duplicates.

@bushpalaneelima ,

 

Glad to hear that! I have moved the case from issues forum to desktop forum. Could you please mark the appropriate reply as a solution? Thank you very much.

 

Regards,

Jimmy Tao

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors