fnCreateDimTables.pq

let fnCreateDimTables = 

  (t as table, optional exclusions as list) =>
    let

      // Get the schema for the table
      Schema = Table.Schema(t), 

      // Select the text columns
      AllTextCols = Table.SelectRows(Schema, each _[Kind] = "text")[Name], 

      // If there are columns to exclude, then exclude them
      TextCols = if exclusions <> null then List.RemoveItems(AllTextCols, exclusions) else AllTextCols, 

      // Figure out the max index to assist with List.Generate()
      MaxIndex = List.Count(TextCols) - 1, 
      
      // Build the list which figures out the unique values and builds a nested table
      cols = 
        
        let
          tbl = t
        in
          List.Generate(
            // Generate a record
            () => [

              // first field is the index
              i = 0, 

              // second field is the table with an index column and the dimension value, sorted in ascending order
              tbl = Table.AddIndexColumn(
                Table.Sort(
                  Table.Distinct(Table.SelectColumns(tbl, TextCols{0}), Comparer.Ordinal), 
                  {{TextCols{0}, Order.Ascending}}
                ), 

                // Set the index to the column name with _ID appended at the end
                TextCols{0} & "_id", 
                1, 
                1
              ), 
              // Third field is the name of the column
              dimension = TextCols{0}
            ], 

            // Generate while the incrementer is less than or equal to the max index
            each [i] <= MaxIndex, 

            // create the next records
            each [
              i = [i] + 1, 
              tbl = Table.AddIndexColumn(
                Table.Sort(
                  Table.Distinct(Table.SelectColumns(tbl, TextCols{[i] + 1}), Comparer.Ordinal), 
                  {{TextCols{[i] + 1}, Order.Ascending}}
                ), 
                TextCols{[i] + 1} & "_id", 
                1, 
                1
              ), 
              dimension = TextCols{[i] + 1}
            ]
          ), 

      // Convert the list of records to table
      ToTable = Table.FromList(cols, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
      
      // Expand the record column
      Expanded = Table.ExpandRecordColumn(
        ToTable, 
        "Column1", 
        {"i", "tbl", "dimension"}, 
        {"i", "tbl", "dimension"}
      ), 

      // Set the data types
      ChangedTypes = Table.TransformColumnTypes(
        Expanded, 
        {{"i", Int64.Type}, {"dimension", type text}}
      )
    in
      ChangedTypes, 
  
  // Documentation
  fnType = type function (t as table, optional exclusions as list) as table
    meta [
      Documentation.Name = "fnCreateDimTables", 
      Documentation.LongDescription
        = "This function takes a table and creates DimTables for all text columns, minus an optional list of columns you choose to exclude."
    ]
in
  Value.ReplaceType(fnCreateDimTables, fnType)