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] )
tamerj1
3 years agoCommunity Champion
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] )- ChiRomeu3 years agoHelper I
Thanks a lot!