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

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.