Keep Duplicates in Summarized Table

[PowerBINoob24]

I created a table using the Summarize function and I would like to keep the duplicates and remove everything else; however, when I go into power query I only see the actual data table that have been imported into the desktop.  Is there a way to keep duplicates and remove everything else in a summarized table?  All of search return solutions for deleting dupicates, not keeping duplicates.

Thanks in advance.

[v-cgao-msft · Accepted solution]

Hi @PowerBINoob24 ,

Please try this:

Table =
VAR _table =
    SUMMARIZE ( 'Table1', 'Table1'[E-Mail], 'Table1'[Value] )
VAR _filter_table =
    FILTER (
        'Table1',
        CALCULATE (
            COUNT ( 'Table1'[E-Mail] ),
            FILTER ( ALL ( 'Table1' ), 'Table1'[E-Mail] = EARLIER ( 'Table1'[E-Mail] ) )
        ) > 1
    )
RETURN
    _filter_table

Table1:

Table:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[sarah-931]

@v-cgao-msft Hello, why VAR _table is created but not used?

[Ashish_Mathur]

Hi,

Share some data (in a format the can be pasted in an MS Excel file) and show the expected result.

[PowerBINoob24]

I can't sahre the data as it's confidential, but the expected result is to essentially retain all rows that contain email addresses that are repeated and remove any that are only listed once, i.e if the email powerbinoob24@nowheredotcom shows up 2,3,4 or more times then keep all those rows.  If it only shows up once don't show the row.

[Ashish_Mathur]

Hi,

This M code works.  It will reduce the rows in the table from 3 to 2 (see 2 images below)

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"E-mail", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"E-mail"}, {{"GroupTables", each _, type table [#"E-mail"=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "CountRows", each Table.RowCount([GroupTables])),
    #"Expanded GroupTables" = Table.ExpandTableColumn(#"Added Custom", "GroupTables", {"E-mail"}, {"E-mail.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded GroupTables", each [CountRows] > 1),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"E-mail.1", "CountRows"})
in
    #"Removed Columns"

[PowerBINoob24]

Thanks for this; however, I've never used M code before.  I assume it's used in the same way as DAX.  I'm not clear though...your code has "source = excel.currentworkbook"  I need to use this in a power BI dashboard, not excel.

[Ashish_Mathur]

You are welcome.  The name of the Table is Data.  Click on Transform data and paste the code there.