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
    tamerj1
    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] )

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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] )
  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    3 years ago

    Hi:

    Assuming your table name = "Data" you can use this caclculated column:

    Unique =
    VAR CountID =
    COUNTROWS(
                   FILTER(ALL(Data),
                   'Data'[Master_ID] = EARLIER(Data[Master_ID])))
    RETURN
    IF(CountID >1,0, 1)
     

    I hope this helps!

    • ChiRomeu's avatar
      ChiRomeu
      Helper I
      3 years ago

      Thank you for info.

      Is it possible to include the first row about the >2 rows?

       

      best regards

       

      • Whitewater100's avatar
        Whitewater100
        Solution Sage
        3 years ago

        YEs, like this?

        Unique R =
        IF(
        COUNTROWS(
                       FILTER(ALL(Data),
                       'Data'[Master_ID] = EARLIER(Data[Master_ID])))
         >1,0, 1)

        If this helps, please mark as solution.Have a nice day!