powerquery

Power Query merged table returns pre-sorted values

I have two tables that I need to merge using an ID column. Both of then have duplicated values, but I only need the max values. So, I sorted the tables in descending order and removed the duplicates. However, after merging the tables, instead of receiving the max value, the merged table returns the first value before sorting.

The tables are like this:

ID value1
a 8.7
a 10.9
b 5.2
c 9.0
ID value2
a 50
b 60
c 80

I sorted the tables and removed the duplicates:

ID value1
a 10.9
b 5.2
c 9.0
ID value2
a 50
b 60
c 80

After the merge, I get this:

ID value1 value2
a 8.7 50
b 5.2 60
c 9.0 80

But I want this:

ID value1 value2
a 10.9 50
b 5.2 60
c 9.0 80

This is how my code looks like:

#first table to be merged
let
    source = Table.Combine({table1, table2}),
    sorted = Table.Sort(source,{{"value1", Order.Descending}}),
    remove_duplicates = Table.Distinct(sorted, {"ID"}),
    table1_final = Table.Sort(remove_duplicates ,{{"ID", Order.Ascending}})

#second table to be merged
    source2 = Table.Combine({table1, table2}),
    sorted2 = Table.Sort(source2,{{"value2", Order.Descending}}),
    remove_duplicates2 = Table.Distinct(sorted2, {"ID"}),
    table2_final = Table.Sort(remove_duplicates ,{{"ID", Order.Ascending}})

#merged table    
    source3 = Table.NestedJoin(table1_final, {"ID"}, table2_final , {"ID"}, "table1_final ", JoinKind.FullOuter),
    expanded = Table.ExpandTableColumn(source3 , "table2_final", {"value2"}, {"value2"})

in
    expanded
Solution

  • Take the first table. Group it to get maximums

    enter image description here

    Merge the second table. Expand value2 column

    enter image description here

    Full code

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"ID"}, {{"value1", each List.Max([value1]), type nullable number}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"ID"}, Table2, {"ID"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"value2"}, {"value2"})
in #"Expanded Table2"