Replace duplicates with null

[PI2023]

Hi everyone, 

I have a table that I need to remove the duplicates in the "Source.Name.1" column but keep the first value when it changes. So source name would be row 1 "1K2321C0172" and nulls until the value changes to "1K2321C0175" and so on at each occurance.

[Kmcdonald]

Hi PI2023,

I think I've achieved what you need. Below what was starting point, I know you have a much bigger dataset but I thought that I'd show you it basic and you could cater it to your scenario. I just made some dummy data, with letters where some are duplicate and some not. I sorted it to show you a visual of how to do this.

Firstly, I added an index column starting from 1.

Then I added a custom column, which was just the index row minus 1. This gives you the previous row value.

Next I merged the query onto itself, referencing the previous row on the original query and the index on the new one.

Expand out the merge, by bringing only the original value column.

Then I added a conditional column, where if the two value columns are equal, then the output would be "null", otherwise bring over the original value.

Lastly, I removed all the unwanted columns, which has given the result of any duplicate with a null value.

[pbix1]

Hi PI2023

This link may help you. You need to have an index column. Then you could use an if statement to compare the value in the current row with the value in the next/previous row.

Value from previous row – Power Query, M language – Trainings, consultancy, tutorials (exceltown.com...