Forum Discussion
Renaming columns with formula
Hi,
I have a data set that has a row with year above Q1, and then blank for Q2, 3 and 4. I have made the row that contains the quater my header. However, I now need to add back in the years, the data set goes from Q2 2020 back to Q1 1985. Rather than manual updating the columns is there a way I can use a query or forumla to do this?
thanks in advance
Phil
Hello philslater0312
this for sure is not that easy as it might look. My code takes all ColumnNames that are containing a "Q" and is renaming them automatically, using 2 variable for quarter and year as trigger for the starting quarter. They are then renamed, adding always 1 quarter. Here the code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Q1 = _t, Q3 = _t, #"Q1 1986" = _t, #"Q2 1986" = _t, Q3_1986 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Q1", type text}, {"Q3", type text}, {"Q1 1986", type text}, {"Q2 1986", type text}, {"Q3_1986", type text}}), ColumnHeadersToChange = List.Select(Table.ColumnNames(#"Changed Type"),each Text.Contains(_,"Q")), QuarterToStart = 1, YearToStart = 1986, CreateNewHeaders = List.Generate ( ()=> [Q=QuarterToStart, Year=YearToStart, Result= "Q"&Text.From(QuarterToStart)&" "&Text.From(YearToStart),Counter=0 ], each [Counter]<= List.Count(ColumnHeadersToChange)-1, (rec)=> [ Counter = rec[Counter]+1, Q= if rec[Q]=4 then 1 else rec[Q]+1, Year = if rec[Q]=4 then rec[Year]+1 else rec[Year], Result= "Q"&Text.From(if rec[Q]=4 then 1 else rec[Q]+1)&" "&Text.From(if rec[Q]=4 then rec[Year]+1 else rec[Year]) ], each [Result] ), RenameColumns =Table.RenameColumns( #"Changed Type",List.Zip({ColumnHeadersToChange,CreateNewHeaders})) in RenameColumnsold names
new names
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
7 Replies
Can you show an example to illustrate what you need?
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
AlB ,
I have attached a couple of screen shots to highlight what I am trying to do. Adding the year to each quater is a bit time consuming and wondered whether there was an easier way to do it.
Hello philslater0312
this for sure is not that easy as it might look. My code takes all ColumnNames that are containing a "Q" and is renaming them automatically, using 2 variable for quarter and year as trigger for the starting quarter. They are then renamed, adding always 1 quarter. Here the code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Q1 = _t, Q3 = _t, #"Q1 1986" = _t, #"Q2 1986" = _t, Q3_1986 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Q1", type text}, {"Q3", type text}, {"Q1 1986", type text}, {"Q2 1986", type text}, {"Q3_1986", type text}}), ColumnHeadersToChange = List.Select(Table.ColumnNames(#"Changed Type"),each Text.Contains(_,"Q")), QuarterToStart = 1, YearToStart = 1986, CreateNewHeaders = List.Generate ( ()=> [Q=QuarterToStart, Year=YearToStart, Result= "Q"&Text.From(QuarterToStart)&" "&Text.From(YearToStart),Counter=0 ], each [Counter]<= List.Count(ColumnHeadersToChange)-1, (rec)=> [ Counter = rec[Counter]+1, Q= if rec[Q]=4 then 1 else rec[Q]+1, Year = if rec[Q]=4 then rec[Year]+1 else rec[Year], Result= "Q"&Text.From(if rec[Q]=4 then 1 else rec[Q]+1)&" "&Text.From(if rec[Q]=4 then rec[Year]+1 else rec[Year]) ], each [Result] ), RenameColumns =Table.RenameColumns( #"Changed Type",List.Zip({ColumnHeadersToChange,CreateNewHeaders})) in RenameColumnsold names
new names
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
