Forum Discussion
ChiRomeu
3 years agoHelper I
Unique value
Dear member, could you please kindly help me to solve the following problem: I would like to get this result in table of power. Best regards Chi
- 3 years ago
HI ChiRomeu
Usually this requires an index column easily added using power query with one click. However, if this is not possible for any reason you can create a new calculated table as follows. (refer to attached sample file)Where 'Data' is the original table
Data New = VAR Items = CONCATENATEX ( Data, Data[MasterId], "|" ) VAR Length = PATHLENGTH ( Items ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = ADDCOLUMNS ( T1, "@Index", [Value], "@MasterID", PATHITEM ( Items, [Value] ) ) VAR T3 = ADDCOLUMNS ( T2, "@UniqueValue", VAR CurrentID = [@MasterID] VAR CurrentIndex = [@Index] VAR FirstIndex = MINX ( FILTER ( T2, [@MasterID] = CurrentID ), [@Index] ) RETURN IF ( CurrentIndex = FirstIndex, 1, 0 ) ) RETURN SELECTCOLUMNS ( T3, "Master ID", [@MasterID], "Unique Value", [@UniqueValue] )
ribisht17
3 years agoSuper User