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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous ,

 

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
Anonymous
Not applicable

idea provided by 

 

@Anonymous ,

 

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

Anonymous
Not applicable

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.
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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