Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ToGM
Frequent Visitor

Datasource column name change

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:

ToGM_0-1670463683629.png

 

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?

 

1 ACCEPTED SOLUTION
hnguy71
Memorable Member
Memorable Member

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:

hnguy71_1-1670467921770.png

 

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:

hnguy71_2-1670467989995.png

 

After column renames:

hnguy71_3-1670468013124.png

 

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

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

2 REPLIES 2
hnguy71
Memorable Member
Memorable Member

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:

hnguy71_1-1670467921770.png

 

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:

hnguy71_2-1670467989995.png

 

After column renames:

hnguy71_3-1670468013124.png

 

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

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
ppm1
Solution Sage
Solution Sage

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

 

Pat

 

Microsoft Employee

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.