Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Eliminating duplicates rows in table

My table has the folowwing data. I only want the row where Col2 has the max value. Is there a method to do this at the Table view level? If not what would be an alternative mehtod?

 

Col1    Col2

1           1

1           2

1           3

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    Please try this code in power query advanced editor,

    let

    Source = #table(
        {"Col1", "Col2"}, 
        {
            {1, 1},
            {1, 2},
            {1, 3},
            {2, 2},
            {2, 3},
            {2, 4},
            {3, 5},
            {3, 4},
            {3, 3}
        }
    ),
   NewTable = Table.Group(
        Source, 
        {"Col1"}, 
        {{"Col2", each List.Max([Col2])}}
    )
in
    NewTable

     

    Best Regards,

    Bof

11 Replies

  • dharmendars007's avatar
    dharmendars007
    Memorable Member
    1 year ago

    Hello Anonymous , 

     

    Please try the below code..

     

    FilteredTable =
    FILTER(YourTable, YourTable[Col2] = MAX(YourTable[Col2]))

     

    If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

     

    Thank You

    Dharmendar S

    LinkedIN 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      dharmendars007, Where does this code get placed?

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    Would you like to group the data by Col1, keeping only the row with the highest value in Col ?

     

    Thanks for dharmendars007's concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:

     

    1\My table(MyTable)

     

    2\Create a new table 

    NewTable = SUMMARIZE(MyTable,MyTable[Col1],"Col2",MAX(MyTable[Col2]))

     

    Best Regards,

    Bof

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Bof, This seems to work. However my existing table is very large so I don't want to duplicate it. Is there a way I can use this summarize function in conjuction with my existing table?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Anonymous ,

         

        Please try this code in power query advanced editor,

        let

    Source = #table(
        {"Col1", "Col2"}, 
        {
            {1, 1},
            {1, 2},
            {1, 3},
            {2, 2},
            {2, 3},
            {2, 4},
            {3, 5},
            {3, 4},
            {3, 3}
        }
    ),
   NewTable = Table.Group(
        Source, 
        {"Col1"}, 
        {{"Col2", each List.Max([Col2])}}
    )
in
    NewTable

         

        Best Regards,

        Bof

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    This M code in Power Query works

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

    Hope this helps.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      I'm getting the following error. Can you tell me what I'm doing wrong?

       

      let
      Source = Oracle.Database("aaaaaa", [HierarchicalNavigation=true]),
      ASOWNER = Source{[Schema="aaaaa"]}[Data],
      TIDDVCU = ASOWNER{[Name="TIDDVCU"]}[Data],
      #"Changed Type" = TIDDVCU.TransformColumnTypes(Source,{{"design_number", Int64.Type}, {"design_version", Int64.Type}}),
      #"Grouped Rows" = Table.Group(#"Changed Type", {"design_number"}, {{"Count", each Table.Max(_,"design_version")}}),
      #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"design_number"}, {"design_version"})
      in
      #"Expanded Count"

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    Anonymous it should be this:

     

    let
Source = Oracle.Database("aaaaaa", [HierarchicalNavigation=true]),
ASOWNER = Source{[Schema="aaaaa"]}[Data],
TIDDVCU = ASOWNER{[Name="TIDDVCU"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(TIDDVCU,{{"design_number", Int64.Type}, {"design_version", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"design_number"}, {{"Count", each Table.Max(_,"design_version")}}),
#"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"design_number"}, {"design_version"})
in
#"Expanded Count"
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      I double checked that design_number is the correct column name.

       

       

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    Anonymous keep in mind, PQ is case-sensitive, make sure the name of the column is the same case. Or delete this step and manually rename the columns.