Mass Data Editing Query/Data Modelling

Hi dcadwallader,

You can try to use below formula which about replace the column type:

Custom functions:

Replace text to type

let 
    ConvertType = (input) => 
    let
        values = {
        {"text", type text},
        {"number", type number},
        {"date", type date},
        {input, type text}
        },
    Result = List.First(List.Select(values, each _{0}=input)){1}
in
    Result
in 
    ConvertType

Replace structure:

let 
    GetStruct = (sourceTable as table, ColumnNamelist as list, ReplaceList as list) =>
     let
        Source = List.Transform(Table.ToList(Table.SelectColumns(Table.Schema(sourceTable),{"Name","Kind"})), each Text.Split(_,",")),
        Change = List.Transform(Source, each {List.First(_), ConvertType(List.Last(_))}),
        Replace= List.Transform(Change, each if List.Contains(ColumnNamelist,List.First(_)) then List.ReplaceMatchingItems(_,ReplaceList) else _)
    in
        Replace
in 
    GetStruct

Use Sample:

let
    Source =Table.TransformColumnTypes(TableName, GetStruct(TableName, {"Column1","Column2","Column3"}, {{type date, type text}}))
in
    Source

Regards,

Xiaoxin Sheng