M Equivalent to LOOKUPVALUE (trying to find first unique value)

[humphra2]

Hi. 

I'm completely new to Power BI and am trying to work out what the M equivalent to the following DAX formula would be.

Unique ThirdParty_Ref = IF(GE_Data[ThirdParty_Ref]<>LOOKUPVALUE(GE_Data[ThirdParty_Ref],GE_Data[Index],GE_Data[Index]-1),1,0)

I am trying to indicate if a value in a particular column is the first unique/distinct instance of that value. If yes, return a value of 1. If not, return a value of 0

Thankyou!

[Anonymous · Accepted solution]

HI @humphra2 ,

You can add a custom column and with function.invoke to looping on the current table and based on index and compare with its result:

#"Added Custom" = Table.AddColumn(#"previous step", "Custom", each if [Index]<>0 then if [ThirdParty_Ref] <> Function.Invoke((_index as number)=> Table.SelectRows(#"previous step",each [Index] = _index-1)[ThirdParty_Ref]{0},{[Index]}) then 1 else 0 else null )

Regards,

Xiaoxin Sheng

[MFelix]

Hi  @humphra2 ,

The best person to answer you this questions is @ImkeF  she's the one to go or M language questions.

@ImkeF , can you please assist on this question for sure you will make it very easily.

Regards

MFelix