fnGetEnvironmentVariable.pq

let
    // Define the base API endpoint for the Dataverse environment
    baseUrl = "https://your-org.crm.dynamics.com/api/data/v9.2/",

    // Define the schema name of the environment variable you want to fetch
    schemaName = "schema_name",  // Replace with the actual schema name

    // Fetch the environment variable definition that matches the given schema name
    definitionQuery = Web.Contents(
        baseUrl, 
        [
            RelativePath = "environmentvariabledefinitions",
            Query = [
                #"$select" = "environmentvariabledefinitionid",
                #"$filter" = "schemaname eq '" & schemaName & "'"
            ],
            Headers = [#"Accept" = "application/json"]
        ]
    ),
    definitionJson = Json.Document(definitionQuery),
    definitionRecord = try definitionJson[value]{0} otherwise null,

    // If a definition was found, fetch the associated environment variable value
    valuesQuery = if definitionRecord <> null then
        Web.Contents(
            baseUrl, 
            [
                RelativePath = "environmentvariablevalues",
                Query = [
                    #"$select" = "value",
                    #"$filter" = "_environmentvariabledefinitionid_value eq " & "'" & definitionRecord[environmentvariabledefinitionid] & "'"
                ],
                Headers = [#"Accept" = "application/json"]
            ]
        )
    else
        null,

    valuesJson = if valuesQuery <> null then Json.Document(valuesQuery) else null,
    valueRecord = try valuesJson[value]{0} otherwise null,

    // Extract the value if available
    envVariableValue = if valueRecord <> null then valueRecord[value] else null,

    // Return the result as a record (schema name and value)
    result = [
        SchemaName = schemaName,
        Value = envVariableValue
    ]
in
    result