get_environment_variables.pq

let
  // Construct the URL to fetch environment variable values
  values_url = Env 
    & "environmentvariablevalues?$select=environmentvariablevalueid,value,_environmentvariabledefinitionid_value", 

  // Retrieve the environment variable values from the OData feed
  values = OData.Feed(values_url, null, [Implementation = "2.0"]), 

  // Construct the URL to fetch environment variable definitions
  definition_url = Env 
    & "environmentvariabledefinitions?$select=schemaname,environmentvariabledefinitionid", 

  // Retrieve the environment variable definitions from the OData feed
  definition = OData.Feed(definition_url, null, [Implementation = "2.0"]), 

  // Perform a Left Outer Join between definitions and values tables 
  // using 'environmentvariabledefinitionid' as the key
  merge = Table.NestedJoin(
    definition, 
    {"environmentvariabledefinitionid"}, 
    values, 
    {"_environmentvariabledefinitionid_value"}, 
    "values", 
    JoinKind.LeftOuter
  ), 

  // Expand the joined column to include the 'value' field from the values table
  expand_values = Table.ExpandTableColumn(merge, "values", {"value"}, {"value"}), 

  // Reorder the columns for better readability
  result = Table.ReorderColumns(
    expand_values, 
    {"environmentvariabledefinitionid", "schemaname", "value"}
  )

in
  result