convert excel formula to power query

[james_james_jam]

hello,

i wish to convert a excel formula into power query format.

i import a CSV file into data model and do calculation within that data model, thats why i cannot use excel formula.

i am able to achieve this in excel using this formula, and extend the row by dragging downwards.

=IF(MATCH(C2,$C$2:$C$17,0)=ROW(C2)-1,G2,0)

explanation:

im wish to create a new column.

$C$2:$C$17 is a column containing unique serial number, it could grow more and more... this example only have 17 row

in english, i wish to check whether C2 is the first time occur within the long list of serial number column, if it is first occurance, then copy cell from of G2,

otherwise set to ZERO.

th serial number is unique, but it can appear a few times in multiple row, i just want to take the value recorded in column G if this guy appear for the first time, if it appear the second time or more, just set to zero.

May i ask for help to convert this formula into power query? 

thank you

[Michal_cwiok]

@james_james_jam, would it be possible if you posted an example table before and after the formula? It will be easier for us to be on the same page with you.

Thanks

[james_james_jam]

Thanks Michal,

im not able to upload whole excel, so i print screen instead. this is the working example i created in excel form, now i scratching my head to convert this into power query form, at least i know it is doable in excel, so i hope i can translate it into power query

the column H is the new column i wish to create. as you can see, serialNO is unique, but some does re-appear in later row. 

[Michal_cwiok]

First occurence means first occurence by date?

[james_james_jam]

Dear Michal,

yes, it is by date, however i know the nature of the data, it has to be sequencial according to time, so we can assume the entry of the data is following real time nature, such that newer data is at last entry