Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
RenameColumns
old 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
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.
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! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.