Eliminating duplicates rows in table

[U156531]

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

[parry2k]

@U156531 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.

[parry2k]

@U156531 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"

[U156531]

I double checked that design_number is the correct column name.

[Ashish_Mathur]

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.

[U156531]

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"

[Ashish_Mathur]

No, I cannot help by just looking at a screenshot.

[v-bofeng-msft]

Hi @U156531 ,

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

[U156531]

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?

[v-bofeng-msft · Accepted solution]

Hi @U156531 ,

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

[dharmendars007]

Hello @U156531 , 

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 

[U156531]

dharmendars007, Where does this code get placed?