Power Query add suffix to column names except the first ones

[Goodkat]

Dear Power BI/ Power Query Community,

I am new to Power Query and now made my first steps in a data model to get away from extensive VBA (which I like a lot, though) and numerous VLookUp. Now I am struggling: I connect data from a .csv file. In that imported data I want to edit the datatype of all columns, except the first 9 columns. I managed to find a solution derived from various posts I read.

HeaderSet (previous step) ColumnsType = Table.TransformColumnTypes(HeaderSet,List.Transform(List.Skip(Table.ColumnNames(HeaderSet),9), each {_, type number}))

As the columns transformed do contain numbers of hours, but this is not depicted in the header, I want to also attach a suffix to these (all except the first 9) column names. I thought I just replicate what worked:

HeaderSet (previous step)

ColumnNames = Table.TransformColumnNames(List.Transform(List.Skip(Table.ColumnNames(HeaderSet),9), each _ & " hours")

But it does not work and I do not know yet, why. I will spent more time on threads and videos to learn about the commands I used in the successful datatype conversion. But if that does not work I would be very grateful if someone from the PowerQuery community could tell me how attaching a suffix to the column names of all except the first 9 columns is possible. That would be great! In the meantime I try to get more background on the commands above.

Thank you!

Best regards, Andreas

[Vijay_A_Verma · Accepted solution]

Use this

= Table.TransformColumnNames(Source,(x)=>if List.Contains(List.Skip(Table.ColumnNames(Source),9),x) then x&"_hours" else x)

[AlexisOlson · Accepted solution]

Another option is to use List.Position.

= Table.TransformColumnNames(HeaderSet, each
      if List.PositionOf(Table.ColumnNames(HeaderSet), _) >= 1
      then _ & " hours" else _
  )

[Goodkat]

Dear Alexis,

Thank you for your reply! It works! Today is a bank holiday in Germany and as I was quite packed with work the last days I did not have the chance to properly test and ultimately understand the approach. But today I was able to implement your approach in my datamodel and understand it.

Your time devoted to my topic is much appreciated. Step by step the datamodel will get better and my understanding of power query will evolve. That is cool!

Have a good day today, wherever you are.

Best regards, Andreas

[Vijay_A_Verma · Accepted solution]

Use this

= Table.TransformColumnNames(Source,(x)=>if List.Contains(List.Skip(Table.ColumnNames(Source),9),x) then x&"_hours" else x)

[Goodkat]

Dear Vijay,

Please accept my apologies for answering delayed. But I wanted to take proper time to understand your approach, implement it into the datamodel, with flexible determination of the number to skip and also comment your lines in my files. As today is a bank holiday in Germany I enjoyed this successful morning with learning by your post!

A big, big thank you to you that you took the time to answer!

I hope you will have a good and insightful day today!

Best regards, Andreas