Forum Discussion

PowerBINoob24's avatar
PowerBINoob24
Icon for Resolver I rankResolver I
3 years ago
Solved

Keep Duplicates in Summarized Table

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

PowerBINoob24's avatar
PowerBINoob24
Icon for Resolver I rankResolver I
3 years ago

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's avatar
Ashish_Mathur
Icon for Super User rankSuper User
3 years ago

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's avatar
    PowerBINoob24
    Icon for Resolver I rankResolver I
    3 years ago

    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's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      3 years ago

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