Forum Discussion

JoannaSK's avatar
JoannaSK
Microsoft Employee
5 months ago
Solved

Create table to remove duplicates

In a direct query data source linked to a cube, I have a table with an id, an email, and several other fields. ID is the unique identifier, and each email can appear multiple times.

IDEmailOther1Other2

1

[email protected]asdasd
2[email protected]asdqwe
3[email protected]werert
4[email protected]qweert
5[email protected]werert

 

I want a table where each email appears once, as the record that has the max id for that email.

IDEmailOther1Other2
2[email protected]asdqwe
4[email protected]qweert
5[email protected]werert

 

Obviously you can get the max id for each email using GROUPBY, but I'm not sure how to then get the rest of the record associated with that calculated id. How do I accomplish this?

Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    Step 1) Create a measure to get the max ID per email

    Max ID per Email =
MAX ( 'YourTable'[ID] )

     

    Step 2) Create measures for the other fields

    Other1 (Latest) =
VAR _MaxID = [Max ID per Email]
RETURN
CALCULATE(
    SELECTEDVALUE( 'YourTable'[Other1] ),
    'YourTable'[ID] = _MaxID
)
    Other2 (Latest) =
VAR _MaxID = [Max ID per Email]
RETURN
CALCULATE(
    SELECTEDVALUE( 'YourTable'[Other2] ),
    'YourTable'[ID] = _MaxID
)

     

    Step 3) Build the visual

    Use a table visual with: 

    • Email
    • [Max ID per Email]
    • [Other1 (Latest)]
    • [Other2 (Latest)]

4 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    5 months ago

    Step 1) Create a measure to get the max ID per email

    Max ID per Email =
MAX ( 'YourTable'[ID] )

     

    Step 2) Create measures for the other fields

    Other1 (Latest) =
VAR _MaxID = [Max ID per Email]
RETURN
CALCULATE(
    SELECTEDVALUE( 'YourTable'[Other1] ),
    'YourTable'[ID] = _MaxID
)
    Other2 (Latest) =
VAR _MaxID = [Max ID per Email]
RETURN
CALCULATE(
    SELECTEDVALUE( 'YourTable'[Other2] ),
    'YourTable'[ID] = _MaxID
)

     

    Step 3) Build the visual

    Use a table visual with: 

    • Email
    • [Max ID per Email]
    • [Other1 (Latest)]
    • [Other2 (Latest)]
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 months ago

    Hi,

    This M code in Power Query works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Email", type text}, {"Other1", type text}, {"Other2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Email"}, {{"Count", each Table.Max(_,"ID")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"ID", "Other1", "Other2"}, {"ID", "Other1", "Other2"})
in
    #"Expanded Count"

    Hope this helps.

     

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    5 months ago

    Hi JoannaSK ,

     

    You can either do the group by transformation at the source level or create a simple measure like MAX(since its DirectQuery) and add all other fields from your table.

     

    Please give kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful

    • JoannaSK's avatar
      JoannaSK
      Microsoft Employee
      5 months ago

      I do not own the source, and the ID field is invisible if I try to connect to the cube via Power Query. Does that mean I can't do the transformation at the source level?

       

      I am not sure what you mean by "create a simple measure like MAX and add all the other fields". Obviously GROUPBY(Table1, Table1[Email], "MaxID", MAXX(CURRENTGROUP(), Table1[ID])) will give me a table with unique emails and the maxid for each one, but how do I add the other fields?