cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
philslater0312
New Member

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

1 ACCEPTED SOLUTION

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
    RenameColumns

old names

Jimmy801_0-1600942668478.png

new names

Jimmy801_1-1600942676874.png

 

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

 

View solution in original post

7 REPLIES 7
AlB
Super User
Super User

Hi @philslater0312 

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 

 

SU18_powerbi_badge

@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.

 

philslater0312_0-1600936352203.png

philslater0312_1-1600936424679.png

 

 

 

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
    RenameColumns

old names

Jimmy801_0-1600942668478.png

new names

Jimmy801_1-1600942676874.png

 

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

 

View solution in original post

Hi, @philslater0312 

What is the relationship between the original column names and the new column names, if I can understand what you say, batch renaming is a simple thing, but I don't understand what you are saying

@ziying35 I have transformed the data to look more like I want it to in the end.  What I am trying to to is create a new column that take the data from the Quarter and add in the year to it.

 

philslater0312_0-1600940969006.png

 

 

Is the easiest way to generate a new column with the years and then combine the two?

Just out of curiousity, why woulf 'quarter' column have 8 distinct values and not 4?

Where do you need the year taken from?

Hi, @philslater0312 , you may consider using Table.TransformColumnNames function to acheive your goal.

Table.TransformColumnNames(table as table, nameGenerator as function, optional options as nullable record) as table

Adapt the func according to your naming rule for columns. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors