hiI 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 - TermordertypeDate totaltaxtotaltaxM38426To Go06/03/202113400M28426To Go06/03/202113400M28426To Go06/03/202113400M48454To Go06/03/202123013.69209.19M48454To Go06/03/202123013.69209.19M48454To Go06/03/202123013.69209.19M48454To Go06/03/202123013.69209.19M48454To Go06/03/202123013.69209.19M48454To Go06/03/202123013.69209.19M48454To Go06/03/202123013.69209.19M48454To Go06/03/202123013.69209.19M48454To Go06/03/202123013.69209.19M48455To Go06/03/20211488.81134.61M48455To Go06/03/20211488.81134.61M48455To Go06/03/20211488.81134.61M48455To Go06/03/20211488.81134.61M48455To Go06/03/20211488.81134.61M48455To Go06/03/20211488.81134.61M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94M58452To Go06/03/202144230.94472.94 So want to add one more column uniquetotal to table to show like this TermordertypeDate totaltaxtotaltaxuniquetotalM38426To Go06/03/2021134000M28426To Go06/03/202113400 M28426To Go06/03/202113400 M48454To Go06/03/202123013.69209.19209.19M48454To Go06/03/202123013.69209.19 M48454To Go06/03/202123013.69209.19 M48454To Go06/03/202123013.69209.19 M48454To Go06/03/202123013.69209.19 M48454To Go06/03/202123013.69209.19 M48454To Go06/03/202123013.69209.19 M48454To Go06/03/202123013.69209.19 M48454To Go06/03/202123013.69209.19 M48455To Go06/03/20211488.81134.61134.61M48455To Go06/03/20211488.81134.61 M48455To Go06/03/20211488.81134.61 M48455To Go06/03/20211488.81134.61 M48455To Go06/03/20211488.81134.61 M48455To Go06/03/20211488.81134.61 M58452To Go06/03/202144230.94472.94472.94M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 M58452To Go06/03/202144230.94472.94 any help will be greatly appreciated

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

VinayPanch00

Frequent Visitor

4 years ago

Solved

how to remove specific data based on unique value

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

Need Help

SpartaBI
4 years ago
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

5 Replies

SpartaBI
Community Champion
4 years ago
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
VinayPanch00
Frequent Visitor
4 years ago
hi SpartaBI
yes the full table has roughly 45 columns and I do need them for other charts

Vinay
- SpartaBI
  Community Champion
  4 years ago
  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.
VinayPanch00
Frequent Visitor
4 years ago
Thank you this really helps
- SpartaBI
  Community Champion
  4 years ago
  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 🙂

Forum Discussion

how to remove specific data based on unique value

5 Replies

Related content

Removing rows with unique values

Remove Text in a specific Column

Remove Text from Specific Column

remove duplicate value within a row

Specific matrix