detect dublicates and attach a string to the second value

[qwertzuiop]

Dear Power BI-Community

Following problem to solve:

Let's assume this table with more than 1'000 records.

As you can see in column A (business unit), there are dublicates that should not be treated as dublicates.

The goal (font in red) is to change in transformation the second occuring value with a string e.g. "-*"

Business Unit	Datum	Value
A	01.01.2021	225
	01.02.2021	241
	01.03.2021	325
	01.04.2021	258
A-*	01.01.2021	12
	01.02.2021	13
	01.03.2021	14
	01.04.2021	15
B	01.01.2021	547
	01.02.2021	564
	01.03.2021	324
	01.04.2021	478
B-*	01.01.2021	22
	01.02.2021	20
	01.03.2021	18
	01.04.2021	14

Any ideas?

Thank you very much for your contribution.

Cheers

qwertzuiop

[Anonymous · Accepted solution]

Hi @qwertzuiop,

You can enter to query edit and 'fill down' on your category fields and add an index column to your table.

Fill values in a column - Power Query | Microsoft Docs

Add an index column - Power Query | Microsoft Docs
After these steps, you can back to the 'data view' side add calculate column with Dax expressions to check the current 'business unit', index field values to remark the raw or duplicate rows.

Regards,

Xiaoxin Sheng

[TomMartens]

Hey @qwertzuiop ,

I'm wondering about the following

• how duplicates are identified
  • my assumption: the combination of business unit and Datum repeat
• how to order the duplicates, from the expected result it is not clear why the 2nd A and the 2nd B are tagged and not the first one.
  You have to be aware that neither Power Query or the vertipaq engine know the concept of a sequence or an implicit row order.

Regards,

Tom