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

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.