Trouble Using a Function

Question

I'm trying to invoke a function that sorts a column in the calling query's table.

I'm basically trying to turn this...

...into this...

I made this simple function:

= () => let
    Source = Table1,
    #"Sorted Rows" = Table.Sort(Source,{{"MatchedWords", Order.Ascending}})
in
    #"Sorted Rows"

...which works basically fine when invoked manually. Except it creates an entirely new table with the result each time it is run. What I want it to do is to perform the sort in the original calling query's table.

Also, when I try to invoke this function from within the calling query's table, Power Query balks due to a circular reference.

What am I doing wrong? How can I use a function to make the sort changes occur to the columns within the calling query's table, and how should I actually call the function?

P.S. I know there are easier ways to do the sort. The sort isn't my real goal. I'm just using the sort as a "simplified" example of a similar requirement that I'm addressing.

Answer 1

The function is referencing objects outside the scope of the query, which is generally not a good idea. Instead, all information required from outside, should be passed as parameters.

You may change your function to:

(MyTable as table, MyColumn as text) => 
let
    Source = MyTable,
    #"Sorted Rows" = Table.Sort(Source,{{MyColumn, Order.Ascending}})
in
    #"Sorted Rows"

This function (I called it Sort) can be called from inside your query:

let
    Source = Table1,
    Sorted = Sort(Source, "MatchedWords")
in
    Sorted

Note that Power Query uses the concept of immutability, meaning that no values are ever changed, only new values are added. E.g. within the query above, you have the original table in Source and a new, sorted table in Sorted. Outside the query, only the sorted version can be referenced (by the name of the query).