Change Type to number - all but not first two Columns in my Column Names list.

[McJasem]

Hi Guys

Isn't there a nifty way to tranform all but two columns in a list to a certain type - Number?

My Table.ColumnNames list only has 12 columns but it canfluctuate, and they are all of the data type Number, but the two first columns in the list. therefore i'd like the transformation to change all but the first two columns and not having to take count of how many columns there are in the list.

[McJasem]

What if I have a dynamic shortened list of all the columns in the table. All but two of those columns in this list should change type to number. how can i reference this list of columns (skip 2) and change those column types to number out of all the table columns?

[mahoneypat]

Please use this expression to do that.  Replace Source with your actual previous step name.

= Table.TransformColumnTypes(Source, List.Zip({List.RemoveFirstN(Table.ColumnNames(Source), 2), List.Repeat({type number}, List.Count(List.RemoveFirstN(Table.ColumnNames(Source), 2)))}))

Pat

[luiz_aquino]

Hi, do you have the same code but instead of changing type, I need to replace the values.

[McJasem]

Hi @mahoneypat 

Thanks for the advice, i tried using the expression and it worked, but my needs changed slightly, and i found a work around in this expression:

= Table.TransformColumnTypes(#"Source", List.Transform (List.RemoveFirstN(List.RemoveLastN(Table.ColumnNames(#"Source"),2),3), each {_, type number}))

This way i have skiped changing type in the three first Columns as well as the two last Columns.

Just wanted to share my workaround!😊

[Jakinta · Accepted solution]

You can try with this step.

= Table.TransformColumnTypes( PriorStepName, List.Transform ( List.RemoveFirstN ( Table.ColumnNames(PriorStepName),2 ) , each {_, type number} ) )

[McJasem]

Thanks @Jakinta

It worked like a charm👌

[StefanoGrimaldi]

ummm, if you run an autodetect step it should assign all columns as number if they are indeed only number, and them set the 2 columns you want to be a different type to that certain type you want, like this those two named column will change to that type and al other column will get number format if they do have umber in them of course. 

if this helped give some kudos, if this solved yout quesiton please mark as solution for others to find it.