Change Data Type on all but 1st column

K

Kirk P.

New member
Joined
Nov 4, 2016
Messages
10
Reaction score
0
Points
0
Location
Minnesota USA
Excel Version(s)
365
I've got a data source with a variable number columns.
The first column data type is always text. All other columns are always decimal. I need to be able to change the data type on all columns but the 1st column to decimal. How would I go about doing this?
 
The code below should do it.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ColNames = Table.ColumnNames(Source), //Get column names
    AllButFirst = List.Skip(ColNames, 1), //Skip first name
    ListOfColsAndTypes = List.Transform(AllButFirst, each {_, type number}), //Generate a list of lists, each list contains the name of the column and the type
    ChangeType = Table.TransformColumnTypes(Source, ListOfColsAndTypes)
in
    ChangeType

I used separate lines to make easier to understand but you can do it all in a single line of code. See file attached.
 

Attachments

  • PQF 7354 - Transform Column Types of all but first column.xlsx
    20.9 KB · Views: 9
Kirk P. said:
Great solution - works perfectly. Thanks
Click to expand...

You can do it all in one line - see line 2 below:

EX:

#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1"),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", List.Transform(List.Skip(Table.ColumnNames(#"Promoted Headers"),1), each {_, type number})),
 
Omezquita, Sweet piece of code, Nice Job!
 
You must log in or register to reply here.
Back
Top