Custom Navigation Property Name Generators

Several data connectors allow you to control the names assigned to relationship columns. Defining a custom relationship column naming format is easy. Ensuring that the generated names do not conflict with existing column names is trickier. Let’s look at how to do both.

Call Me Back (please)

To replace the default navigation property naming scheme with your own, register a NavigationPropertyNameGenerator callback function with the data connector. Not every data connector allows this, so using a supported data connector is a prerequisite.

Typically, this callback is registered with the data connector in the connector’s options record. For example, Sql.Database‘s options are passed in as its (optional) third argument:

Source = Sql.Database(
  serverName, 
  databaseName,
  [NavigationPropertyNameGenerator = YourCustomNameGeneratingFunction])
)

Data In and Back

The callback will be invoked when relationship column names are needed for a table. Two arguments will be passed to it: The first, a list of columns present in the table. The second, a list of relationship detail records, one per relationship that needs a column name. The function is expected to return a list of column names to use for these relationships.

The relationship details records are shaped like:

[

  SourceTableName = text, // name of the current table 
  SourceKeys = {text}, 
  TargetTableName = text, // name of the table this relationship points to
  TargetKeys = {text}, 
  SingleTarget = logical // always true, except in some legacy cases
]

For example:

[
  SourceTableName = "Parent",
  SourceKeys = { "ParentID" },
  TargetTableName = "Child",
  TargetKeys = { "fParentID" },
  SingleTarget = true
]

(At least, this is true for the connectors I tested. As the relationship columns feature is connector-specific, it’s possible that these behaviors, or others mentioned in this post, could vary for other connectors.)

Make It Work (and work well!)

Say you’d like relationship column names in the format of: OtherTableName(KeysFromThisTable->KeysInOtherTable). You can easily define function that takes a single relationship details record and concatenates together the relevant information into a string aligning with the desired format.

GenerateBaseNameForRelationship = (relationship as record) as text =>
  relationship[TargetTableName] 
  & "(" 
  & Text.Combine(relationship[SourceKeys], ",") 
  & "->" 
  & Text.Combine(relationship[TargetKeys], ",") 
  & ")"

Once you have a function that produces the desired name for a single relationship detail, you can easily apply it to the entire list of those details by using List.Transform.

Easily, except that is for one catch.

Preventing Duplicates

It’s technically possible that a name your generation function outputs will conflict with the name of a column that’s already in the table. It’s also possible that multiple names your function generates could be identical. Either case will conflict with the hard-fast rule that no duplicates are allowed in a table’s column names.

To guard against this, you need some way to guarantee that the names your callback returns do not conflict with either existing column names or other names you’ve generated for the same table.

One way to do this is to add a numeric suffix to differentiate what otherwise would be duplicate names. So, if ColumnName is already in the table and the name your generation formula wants to use is ColumnName, then change that latter name to ColumnName 2. If a column by that name is also already present, then try ColumnName 3, then ColumnName 4, and so forth, until a usable name is found.

All Put Together

The below M function puts these two pieces together. RelationshipColumnBaseNames uses List.Transform to apply GenerateBaseNameForRelationship to each relationship details record, outputting a list of candidate relationship column names. These are then passed to EnsureUniqueness, which handles affixing numeric suffixes, where needed.

(existingColumns as list, relationships as list) as list =>
let

  // Defines the base name to use for a single relationship column
  // Input: a single relationship details record
  // Output: candidate name to use for this relationship's column
  GenerateBaseNameForRelationship = (relationship as record) as text =>
    relationship[TargetTableName] 
    & "(" 
    & Text.Combine(relationship[SourceKeys], ",") 
    & "->" 
    & Text.Combine(relationship[TargetKeys], ",") 
    & ")",

  // Computes the base names to use for all relationship columns by applying GenerateBaseNameForRelationship to each relationship detail record
  // Input: list of relationship details records
  // Output: list of candidate relationship column names
  RelationshipColumnBaseNames = (relationships as list) as list =>
    List.Transform(relationships, GenerateBaseNameForRelationship),

  // Revises base names, as needed, to ensure that they are unique in the table
  // Inputs:
  // - Names of columns already in the table, which by definition are guaranteed to be unique (existingUniqueNames)
  // - Candidate names for the relationship columns (newBaseNames)
  // Output: list of relationship column names, with numeric suffixes added, where needed, to ensure uniqueness
  EnsureUniqueness = (existingUniqueNames as list, newBaseNames as list) as list =>
    let 
      GenerateUniqueName = (data as list, baseName as text, occurrence as number) as text =>
        let
          CandidateName = baseName & (if occurrence > 1 then " " & Text.From(occurrence) else "")
        in
          // Use the candidate name, if not already in use; otherwise, try a new candidate name with an (incremented) suffix
          if not List.Contains(data, CandidateName) then CandidateName else @GenerateUniqueName(data, baseName, occurrence + 1)
    in
      // Progresses one at a time through the new base column names, generating a unique name for each
      List.Accumulate(
        newBaseNames, 
        {},
        (state, current) => state & {GenerateUniqueName(existingUniqueNames & state, current, 1)}
      )
in
  EnsureUniqueness(
    existingColumns, 
    RelationshipColumnBaseNames(relationships)
  )

The format output by GenerateBaseNameForRelationship can be changed to anything you’d like. Maybe you’d prefer that all relationship column names start with “Rel” or only contain the key names from the other table, or something else—go for it.

Hopefully this will help when you want to take control over how relationship columns are named!