colRenamesAndTypes Example | manage renaming columns and setting column data types

colRenamesAndTypes.pq

let
    colTypes = [
        #"any" = Any.Type,
        #"date" = Date.Type,
        #"time" = Time.Type,
        #"datetime" = DateTime.Type,
        #"logical" = Logical.Type,
        #"number" = Number.Type,
        #"currency" = Currency.Type,
        #"integer" = Int64.Type,
        #"percentage" = Percentage.Type,
        #"text" = Text.Type
    ],
    /* wsInputs can be replaced with an Excel table, loaded into Power Query */
    wsInputs = Table.FromRows(
        {
            {"LastModified", "Last Modified Date", "date"},
            {"Owner", "Owner", "text"},
            {"NextReview", "Review Date", "date"},
            {"WorkingTitle", "Working Title", "text"},
            {"Revision", "Revision Number", "integer"},
            {"Budget", "Budget Amount", "currency"},
            {"Cost TD", "Cost to date Amount", "number"}
        },
        type table [oldColName = text, newColName = text, colType = text]
    ),
    Source = Table.FromRows(
        {
            {#date(2024, 1, 21), "Melissa", #date(2025, 1, 21), "To be determined", 1, 1000, 156.89},
            {#date(2011, 5, 2), "Sam", null, "Close encounters", 12, 500, 873.03}
        },
        Table.Column(wsInputs, "oldColName")
    ),
    colNames = Table.ColumnNames(Source),
    ValidNames = Table.SelectRows(wsInputs, each List.Contains(colNames, [oldColName])),
    Renamed = Table.RenameColumns(Source, List.Zip({ValidNames[oldColName], ValidNames[newColName]})),
    Transform = Table.TransformColumnTypes(
        Renamed,
        List.Zip({
            ValidNames[newColName],
            List.Transform(ValidNames[colType], each Record.FieldOrDefault(colTypes, _, Any.Type))
        })
    )
in
    Transform