Forum Discussion
ChiRomeu
Helper I
4 years agoUnique 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
- 4 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
Community Champion
4 years agoHI 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] )ChiRomeu
Helper I
4 years agoThanks a lot!