fnCreateDimID.pq

let fnCreateDimID = 
    (fact as table, col_name as text)=>

    let
        fnCreateDim = (fact as table, col_name as text)=>

        let
            removed_cols = Table.SelectColumns(fact,{col_name}),
            dedupe = Table.Distinct(removed_cols),
            sorted = Table.Sort(dedupe,{{col_name, Order.Ascending}}),
            id_name = col_name & "_id",
            add_index = Table.AddIndexColumn(sorted, col_name & "_id", 1, 1, Int64.Type),
            order = Table.ReorderColumns(add_index,{id_name, col_name})
        in
            order,

        dim= fnCreateDim(fact, col_name),
        Join = Table.Join(fact, col_name, dim, col_name),
        Remove = Table.RemoveColumns(Join, col_name)
    in
        Remove, 
  fnType = type function (fact as table, col_name as text) as table
    meta [
      Documentation.Name = "fnCreateDimID", 
      Documentation.LongDescription
        = "This take a table and a column in that table. It then replaces the column with a key."
    ]
in
  Value.ReplaceType(fnCreateDimID, fnType)