Forum Discussion

ChiRomeu's avatar
ChiRomeu
Helper I
3 years ago
Solved

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
  • tamerj1's avatar
    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] )