Forum Discussion

james_james_jam's avatar
james_james_jam
New Member
8 years ago

convert excel formula to power query

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

 

4 Replies

  • Michal_cwiok's avatar
    Michal_cwiok
    Icon for Resolver II rankResolver II
    8 years ago

    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's avatar
      james_james_jam
      New Member
      8 years ago

       

       

       

      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.