CODEWITHSUNDEEP
powerbipowerquerym
Luca Casciola
Luca Casciola

Reputation: 23

PowerBI: Change type to multiple columns at the same time

I'm working in the Power Query Editor in Power BI. I have a table called Source where the columns have this order:

| Country | Attribute | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 |

You can see the table in the image below:

Image I would like to change the column type to every column from "2014" to "2021". I know it is possible to do that expressing each column separately using the command:

= Table.TransformColumnTypes(
    Source,
    {{"2014", type number}, {"2015", type number}, {"2016", type number}, {"2017", type number}, {"2018", type number}, {"2019", type number}, {"2020", type number}, {"2021", type number}}
  )

but I would like to set up a command that does that in an automated fashion. I am trying to do something like:

= Table.TransformColumnTypes(
    Source,
    each( {List.Range(Table.ColumnNames(Source), 2, List.Count(Table.ColumnNames(Source)) as number),
    type number} )
  )

So I am trying to repeat the command Table.TransformColumnTypes for "each" value of:

List.Range(Table.ColumnNames(Source), 2, List.Count(Table.ColumnNames(Source)) as number)

that basically select every column header name in the range from the third column to the last one.

I tried to write it down in many ways, using also $(...).each(function(...)) or using other list. functions but none of them seems to work.

What can I do?

Upvotes: 2

Views: 8690

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You're on the right track with the Table.ColumnNames function.

Give this a try:

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

The List.RemoveFirstN function removes the first N = 2 columns from your list of column names and then you change each remaining column to type number.

Upvotes: 6

Related Questions