Change Column Type between two column names

[Jukotik]

Hello,

I currently have a dataset that is changing regularly. I have new columns that are created in this dataset. These columns are always between two other columns names ("_1" and "BD Errors"). I need to change the datatype of these columns or do the sum of these columns but to do so, the formula I have is integrating the name of each column:

= Table.TransformColumnTypes(#"Removed Bottom Rows",{{"1 Errors", Int64.Type}, {"10 Errors", Int64.Type}, {"1001 Errors", Int64.Type}, {"1002 Errors", Int64.Type}, {"1006 Errors", Int64.Type}, {"1008 Errors", Int64.Type}, {"1011 Errors", Int64.Type}, {"1012 Errors", Int64.Type}, {"103 Errors", Int64.Type}, {"104 Errors", Int64.Type}, {"110 Errors", Int64.Type}, {"115 Errors", Int64.Type}, {"116 Errors", Int64.Type}, {"126 Errors", Int64.Type}, {"127 Errors", Int64.Type}, {"128 Errors", Int64.Type}, {"200 Errors", Int64.Type}, {"203 Errors", Int64.Type}, {"205 Errors", Int64.Type}, {"206 Errors", Int64.Type}, {"207 Errors", Int64.Type}, {"208 Errors", Int64.Type}, {"210 Errors", Int64.Type}, {"211 Errors", Int64.Type}, {"212 Errors", Int64.Type}, {"220 Errors", Int64.Type}, {"221 Errors", Int64.Type}, {"222 Errors", Int64.Type}, {"226 Errors", Int64.Type}, {"305 Errors", Int64.Type}, {"306 Errors", Int64.Type}, {"307 Errors", Int64.Type}, {"308 Errors", Int64.Type}, {"310 Errors", Int64.Type}, {"312 Errors", Int64.Type}, {"322 Errors", Int64.Type}, {"4 Errors", Int64.Type}, {"400 Errors", Int64.Type}, {"405 Errors", Int64.Type}, {"406 Errors", Int64.Type}, {"407 Errors", Int64.Type}, {"408 Errors", Int64.Type}, {"409 Errors", Int64.Type}, {"410 Errors", Int64.Type}, {"411 Errors", Int64.Type}, {"412 Errors", Int64.Type}, {"422 Errors", Int64.Type}, {"505 Errors", Int64.Type}, {"506 Errors", Int64.Type}, {"507 Errors", Int64.Type}, {"508 Errors", Int64.Type}, {"510 Errors", Int64.Type}, {"511 Errors", Int64.Type}, {"512 Errors", Int64.Type}, {"522 Errors", Int64.Type}, {"904 Errors", Int64.Type}, {"908 Errors", Int64.Type}, {"???? Errors", Int64.Type}})

This is an issue because when a new column appear then the formula is not working. I'd like to have a formula that is not depending on the column name anymore. I was thinking of having a formula where it's taking the columns between "_1" and "BD Errors" as these two columns are never changing. Hope you'll understand my issue because currently I need to change the PBI every week as it's changing every time and I wish to avoid these time consumption tasks.

[v-tianyich-msft · Accepted solution]

Hi @Jukotik ,

There is no method of calculating between two columns, but you can use the Table.SelectColumns function. This function allows you to select specific columns in a table. If you have a series of consecutive columns, you can select them by creating a list with the column names.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.