How to make a default values

Guide to Setting Default Values

This guide will provide a comprehensive overview of the process for setting default values in various contexts, including data tables, PowerQuery, and more.

1. Establishing Relationships

To set default values for data tables, you will need a table structured similarly to the following example:

County	County Variation
Dublin	dublin1
Dublin	dublin2
Dublin	dublin3

Once you have this table, you can establish a relationship between the second column ("County Variation") and your data table(s). Finally, you can utilize the first column ("County") for your slicer.

2. Custom Column in PowerQuery

In PowerQuery, you can add a Custom Column using the following expression:

        Text.Proper(Text.Remove([Your Column], {"0".."9"} ))
      

The above expression will capitalize the first letter of each word in the specified column while removing any numeric characters.

3. Splitting Strings and Sorting

To handle more complex scenarios, you can use an expression that splits the string by specific characters and then sorts the resulting list based on the length of each element. The following expression achieves this:

        Text.Proper(
          List.Last(
            List.Sort(
              Text.SplitAny([County Variation], ". 0123456789"),
              (x, y) => Value.Compare(Text.Length(x), Text.Length(y))
            )
          )
        )
      

This expression will return the longest element from the sorted list, providing a more robust default value extraction method.

4. Handling Specific Cases

To address specific cases, such as "Codublin," you can use a more intricate expression that considers various conditions. The following expression demonstrates this approach:

        let
          txt = Text.Trim(Text.Lower([#"Address 1: County"])),
          checkCo = if not Text.StartsWith(txt, "cork") and Text.StartsWith(txt, "co") then Text.AfterDelimiter(txt, "co") else txt,
          result = Text.Proper(
            List.Last(
            List.Sort(
              Text.SplitAny(checkCo, ". 0123456789"),
              (x, y) => Value.Compare(Text.Length(x), Text.Length(y))
            )
           )
          )
        in
          result
      

This expression handles cases where the county name is preceded by "co" or "cork."

5. Optimal Solution for Finite Lists

In situations where you have a finite list of county names, the ideal approach is to loop through the list and match the county name based on containment. This method ensures the most accurate default value extraction.