how to remove specific data based on unique value

[VinayPanch00]

hi

I need help as you see from my rank, either DAX or power Query will work for me.

I have a huge table, let's call it BI, the table is sorted by date, one of the columns "order" gets repeated with totaltax, what I want is a way to add one more column which will show totaltax only once and the rest are blank. 

My original table is - 

Term	order	type	Date	total	tax	totaltax
M3	8426	To Go	06/03/2021	134	0	0
M2	8426	To Go	06/03/2021	134	0	0
M2	8426	To Go	06/03/2021	134	0	0
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8455	To Go	06/03/2021	148	8.81	134.61
M4	8455	To Go	06/03/2021	148	8.81	134.61
M4	8455	To Go	06/03/2021	148	8.81	134.61
M4	8455	To Go	06/03/2021	148	8.81	134.61
M4	8455	To Go	06/03/2021	148	8.81	134.61
M4	8455	To Go	06/03/2021	148	8.81	134.61
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94

So want to add one more column uniquetotal to table to show like this 

Term	order	type	Date	total	tax	totaltax	uniquetotal
M3	8426	To Go	06/03/2021	134	0	0	0
M2	8426	To Go	06/03/2021	134	0	0
M2	8426	To Go	06/03/2021	134	0	0
M4	8454	To Go	06/03/2021	230	13.69	209.19	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8454	To Go	06/03/2021	230	13.69	209.19
M4	8455	To Go	06/03/2021	148	8.81	134.61	134.61
M4	8455	To Go	06/03/2021	148	8.81	134.61
M4	8455	To Go	06/03/2021	148	8.81	134.61
M4	8455	To Go	06/03/2021	148	8.81	134.61
M4	8455	To Go	06/03/2021	148	8.81	134.61
M4	8455	To Go	06/03/2021	148	8.81	134.61
M5	8452	To Go	06/03/2021	442	30.94	472.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94
M5	8452	To Go	06/03/2021	442	30.94	472.94

any help will be greatly appreciated

[VinayPanch00]

Thank you this really helps

[SpartaBI]

@VinayPanch00  my pleasure 🙂
P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

[VinayPanch00]

hi @SpartaBI 

yes the full table has roughly 45 columns and I do need them for other charts

Vinay

[SpartaBI]

@VinayPanch00 ok, did you see the solution I shared? Basically I need a column to sort the duplicate rows. I added an index column but maybe you already have something like that in your table so you could use that instead of the index column I added and replace it in the code.
Please look at the solution I shared.

[SpartaBI · Accepted solution]

@VinayPanch00 why do you have those duplicate rows in the first place? What's the meaning of the same 2 rows in your data? Do you have more columns there that distinguish beteween those rows? 
If not, and you need to keep those duplicates, you will need to add another column in your datasource or Power Query to be unique (at least unique between the groups of duplicates ) and then it can be solved. For example, I added a simple index column in Power Query and then continued to solve this in DAX calculatd column.
here is a link to download the file with the solution:
how to remove specific data based on unique value 2022-07-18.pbix
 

      

Showcase Report – Contoso By SpartaBI