Forum Discussion

qwertzuiop's avatar
qwertzuiop
Icon for Advocate III rankAdvocate III
4 years ago
Solved

detect dublicates and attach a string to the second value

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 UnitDatumValue
A01.01.2021225
 01.02.2021241
 01.03.2021325
 01.04.2021258
A-*01.01.202112
 01.02.202113
 01.03.202114
 01.04.202115
B01.01.2021547
 01.02.2021564
 01.03.2021324
 01.04.2021478
B-*01.01.202122
 01.02.202120
 01.03.202118
 01.04.202114

 

Any ideas?

 

Thank you very much for your contribution.

 

Cheers

qwertzuiop

Need Help

2 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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