Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

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. 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors