cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Goodkat
Frequent Visitor

Power Query add suffix to column names except the first ones

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

2 ACCEPTED SOLUTIONS
Vijay_A_Verma
Community Champion
Community Champion

Use this

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

View solution in original post

AlexisOlson
Super User
Super User

Another option is to use List.Position.

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

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

Another option is to use List.Position.

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

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
Community Champion
Community Champion

Use this

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

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

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.