Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |