Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Power bi data - removing duplicates.

‎05-29-2020 10:57 AM

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
Labels:
Message 1 of 5
799 Views
0
1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support
In response to Anonymous

‎06-02-2020 06:28 PM

@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

Message 5 of 5
736 Views
4 REPLIES 4
Anonymous
Not applicable

‎06-02-2020 09:38 AM

idea provided by 

 

Message 4 of 5
748 Views
0
v-yuta-msft
Community Support
Community Support
In response to Anonymous

‎06-02-2020 06:28 PM

@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

Message 5 of 5
737 Views
Anonymous
Not applicable

‎06-01-2020 06:54 AM
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.
Message 3 of 5
761 Views
0
v-yuta-msft
Community Support
Community Support

‎06-01-2020 01:49 AM

@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

Message 2 of 5
765 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

View All