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

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"

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

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