getMatchedValue Function

// Function: getMatchedValue
// Purpose: Returns a value from a column based on a match to another column in the same row in another table
// Parameters: 
//      sourceTable: table - The table containing the source column
//      sourceColumn: text - The name of the column from which to retrieve the value
//      matchColumn: text - The name of the column used for matching
//      matchValue: any - The value to match in the matchColumn
//      resultTable: table - The table containing the result column
//      resultColumn: text - The name of the column to retrieve the value from
// Return Type: any - The value from the resultColumn matching the matchValue in the matchColumn

(getMatchedValue as function) =>
    (sourceTable as table, sourceColumn as text, matchColumn as text, matchValue as any, resultTable as table, resultColumn as text) as any =>
    let
        // Filter the result table based on the matchValue in the matchColumn
        filteredResult = Table.SelectRows(resultTable, each _[matchColumn] = matchValue),
        // Get the value from the resultColumn where the match occurred
        matchedValue = filteredResult{0}[resultColumn]
    in
        matchedValue

Example Usage:

let
    sourceTable = #table({"ID", "Name"}, {{1, "John"}, {2, "Alice"}}),
    resultTable = #table({"ID", "Age"}, {{1, 30}, {2, 25}}),
    matchedValue = getMatchedValue(sourceTable, "Name", "ID", 1, resultTable, "Age")
in
    matchedValue

In this example, the getMatchedValue function retrieves the Age corresponding to the Name "John" from the sourceTable and resultTable.