Forum Discussion

markpendlebury's avatar
markpendlebury
Frequent Visitor
9 years ago
Solved

Remove Rows in Dataset based on most recent ID and email address

Hi everyone hope someone can help me.....I'm pretty new to PowerBI but loving how flexible it is. However, as anything new I've come up against something I think should be pretty simple.   I have a...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi markpendlebury

     

    I think this calculated table might be getting close.  Just replace my Table2 with the name of your table

     

    Table 3 = SELECTCOLUMNS(
            FILTER(
                CROSSJOIN(
                    SELECTCOLUMNS( 
                        SUMMARIZECOLUMNS(
                            'Table2'[Email],
                            "MAX ID",
                            MAX('Table2'[ID])
                            ),
                            "MAX Email",[Email],
                            "MAX ID",[MAX ID]
                          ) ,
            'Table2'
            ),
            'Table2'[Email]=[MAX Email] && 
            Table2[ID] = [MAX ID]
            ),
            "ID",[ID],
            "Email" , [MAX Email]
            )
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
9 years ago

markpendlebury,

 

You could modify the code in Advanced Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MnaoqKzSS87PVYrViVYyAooZm5g6JCYlw8WMsagzAYoB1TgAxeFipkCxlORUBxNTM7iYGbq6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Email = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Email", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Email"}, {{"Data", each _, type table}}),
    Transformed = Table.TransformColumns(#"Grouped Rows", {{"Data", each Table.AddIndexColumn(Table.Sort(_, {{"ID", Order.Descending}}), "Rank", 1, 1)}}),
    #"Expanded IDs" = Table.ExpandTableColumn(Transformed, "Data", {"ID", "Rank"}, {"ID", "Rank"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded IDs", each ([Rank] = 1)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"ID", Order.Ascending}})
in
    #"Sorted Rows"
markpendlebury's avatar
markpendlebury
Frequent Visitor
9 years ago

v-chuncz-msft Thanks for the answer. In the end I didn't attempt this option as I wasn't sure how or which elements to incorporate and I got the other solution working.

 

I do potentially like this option as it would solve the problem without creating a new table.