fnCreateMultiColDim.pq

let
  fnCreateMultiColDim = (t as table, dim_groups as list, id_names as list) =>
    let
      // This nested function creates the individual dimension tables within the List.Generate() run
      fnMakeDim = (tbl as table, dim_cols as list, id_name as text) =>
        let
          // It add an index column after selecting the columns and deduplicating.
          ChooseAndProcess = Table.AddIndexColumn(
            Table.Distinct(Table.SelectColumns(t, dim_cols)), 
            id_name, 
            1, 
            1
          ), 
          // Puts the id column at the beginning 
          dim = Table.ReorderColumns(ChooseAndProcess, List.Combine({{id_name}, dim_cols}))
        in
          dim, 
      // This checks how many dim groups there are in the nested list
      MaxGroupIndex = List.Count(dim_groups) - 1, 
      Dims = List.Generate(
        () => [
          // Start at index 0
          i = 0, 
          // Get the first table, pass in the first list of dim columns and the first id name
          dim_tbl = fnMakeDim(t, dim_groups{0}, id_names{0})
        ], 
        // As long  as the iterator is less than or equal to the max index, keep going
        each [i] <= MaxGroupIndex, 
        // For the next i
        each [
          // increment i
          i = [i] + 1, 
          // process the next table
          dim_tbl = fnMakeDim(t, dim_groups{[i] + 1}, id_names{[i] + 1})
        ]
      ), 
      // Convert the list of records to a table
      Convert_To_Table = Table.FromList(
        Dims, 
        Splitter.SplitByNothing(), 
        null, 
        null, 
        ExtraValues.Error
      ), 
      // Expand the records
      Expanded = Table.ExpandRecordColumn(
        Convert_To_Table, 
        "Column1", 
        {"i", "dim_tbl"}, 
        {"i", "dim_tbl"}
      ), 
      // Change type of the iterator column
      Changed_Type = Table.TransformColumnTypes(Expanded, {{"i", Int64.Type}})
    in
      Changed_Type, 
  // Documentation
  fnType = type function (t as table, dim_groups as list, id_names as list) as table
    meta [
      Documentation.Name = "fnCreateMultiColDim", 
      Documentation.LongDescription = "This function takes a table, a nested list of lists of columns to group together as dimension tables,#(lf) "
        & "and an ordered list of id names for the new dimension tables. It returns a nested table with dimension tables."
    ]
in
  Value.ReplaceType(fnCreateMultiColDim, fnType)