Datasource column name change

[ToGM]

My datasource is a folder of excel files.  Each month a new file is added to the folder.  There is a column name that has changed in the excel files going forward, and PowerBi is sending an error stating:

The name of the table is now "Legal First Name"

The temporary solution was to manually change the column name in the Excel file, but I'm curious as to how this can be automated through PowerQuery.  Is there a way to take in two possible column names?

[hnguy71 · Accepted solution]

Hi @ToGM ,

It's possible but you'll have to apply a few extra steps. Here's how I would do it.

First, you'll have to create a list that holds the old column name(s) and what you want to replace it with. Here's an example:

Here's the sample copy+paste code the table above:

let
    // Table name is "Rename_Me"
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyk3MzFHSUXIF07E60UpumUXFJQp+ibmpQGEkDkjOJzU9MUcBn4qAjPw8kHBIak5qAZgNEkXwsMjkVGKIRmUWAMWApIJzfgpQKBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SourceName = _t, StandardName = _t])
in
    Source

Next, this is the tricky part... in your "transform sample query", for each file that it gets loaded, you're going to check if any of those columns meets this criteria, if so, we'll rename them.

Before column renames:

After column renames:

Here's the sample copy+paste code for it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8klNT8xRcMssKi5R8EvMTVXSUfJJRLAdU1KKUouLgayQ1JycSiAdlVkAJHMTM3OUYnWilQyBHCMgNgZiEyA2BWIzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    // Ensure there's no field name hardcoding. Let's get the list of column names.
    GetListOfColumnNames = Table.ColumnNames(PromotedHeaders),

    // Get matching column names. MUST BE EXACT!
    SelectMatchingColumns = Table.SelectRows(Rename_Me, (x) => List.Contains(GetListOfColumnNames, x[SourceName])),

    // Convert list to rows
    ConvertMatchToRows = Table.ToRows(SelectMatchingColumns),

    // Renames all EXACT MATCHED found column names
    RenameColumns = Table.RenameColumns(PromotedHeaders, ConvertMatchToRows)
in
    RenameColumns

[ppm1]

Please see this video - https://www.youtube.com/watch?v=nscUeOL5m90

Pat