cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Invoking function causes "Expression.Error: We cannot convert a value of type Record to type Number"

 

 

Hi, I'm trying to create a function that will rename all columns in a table., but when I invoke the function (from both the query editor and navigation panel) I get the folliwng error:

 

An error occurred in the ‘fnRenameColumns’ query. Expression.Error: We cannot convert a value of type Record to type Number.
Details:
Value=Record
Type=Type

 

The function is:

 

 

let
    fnRenameColumns = (tableName as table) =>
let
    Source = tableName,
    ColumnNames = Table.FromList(Table.ColumnNames(Source)),
    Transform = Table.AddColumn(ColumnNames,"New Value",each Text.Upper(Text.Replace([Column1],"_"," "))),
    RenameColumns = Table.RenameColumns(tableName,Table.ToRows(Transform),[Comparer = Comparer.OrdinalIgnoreCase])
in
    RenameColumns
in 
    fnRenameColumns

 

 

Any ideas? Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Invoking function causes "Expression.Error: We cannot convert a value of type Record to typ

So why do you want to rename your columns, is it:

1. Because you get the error of duplicate column names, or

2. You want to rename your columns for another reason, but as a result you get duplicate column names?

 

In any case if you want to rename your columns so there won't be any duplicates, then you need to find some other solution for any duplicates, typically that would be adding a seqeunce number, e.g. if you have multiple "INFO CARD LINK" columns, then the first can be "INFO CARD LINK", the second "INFO CARD LINK.1", the third "INFO CARD LINK.2"  and so on.

 

That would require a complete different approach.

 

Note: in Power Query, column names are case-sensitive, but when loading the result, you'll get an error (I guess that is the error you mentioned in your previous post).

Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

Re: Invoking function causes "Expression.Error: We cannot convert a value of type Record to typ

Technically spoken you provide a record [Comparer = Comparer.OrdinalIgnoreCase] as the third argument for Table.RenameColumns, where you can only provide a number: the value for MissingField.

 

Edit: you code works fine if you remove that third argument.

 

My idea would be:

 

let
    fnRenameColumns = (tableName as table) =>
let
    Source = tableName,
    ColumnNames = Table.ColumnNames(Source),
    Transform = List.Transform(ColumnNames, each {_, Text.Upper(Text.Replace(_,"_"," "))}),
    RenameColumns = Table.RenameColumns(tableName,Transform)
in
    RenameColumns
in 
    fnRenameColumns

 

 

Specializing in Power Query Formula Language (M)
Highlighted
Helper III
Helper III

Re: Invoking function causes "Expression.Error: We cannot convert a value of type Record to typ

Thanks, so I added that 3rd argument because when I don't use it I get this error:

 

An error occurred in the ‘fnRenameColumns’ query. Expression.Error: The field 'INFO CARD LINK' already exists in the record.
Details:
    Name=INFO CARD LINK
    Value=

 

Highlighted
Community Champion
Community Champion

Re: Invoking function causes "Expression.Error: We cannot convert a value of type Record to typ

So why do you want to rename your columns, is it:

1. Because you get the error of duplicate column names, or

2. You want to rename your columns for another reason, but as a result you get duplicate column names?

 

In any case if you want to rename your columns so there won't be any duplicates, then you need to find some other solution for any duplicates, typically that would be adding a seqeunce number, e.g. if you have multiple "INFO CARD LINK" columns, then the first can be "INFO CARD LINK", the second "INFO CARD LINK.1", the third "INFO CARD LINK.2"  and so on.

 

That would require a complete different approach.

 

Note: in Power Query, column names are case-sensitive, but when loading the result, you'll get an error (I guess that is the error you mentioned in your previous post).

Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Helper III
Helper III

Re: Invoking function causes "Expression.Error: We cannot convert a value of type Record to typ

ah, I get it now... silly error on my part.  My objective was to just to imporve the readability of column names, but that renaming has created a duplicate that I didn't originaly see.  I should have actually though about what the error message was telling me...

 

Your solution works perfectly and I greatly apprecaite your expert help!

 

 

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors