M code question

[Anonymous]

Hello M Code experts  -   I have what I think is a simple question.   I am trying to get my values on a chart to be the same format.

The first two are fine.   The Week is what I am trying to get correct  (see orange text).   Any help is appreciated!  

Essentially like this:   Q1 - 2021      Jan - 2021    Week 20 - 2021

InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & "-" & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & "-" & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date],1)),
InsertWeekInCal = Table.AddColumn(InsertWeekNumber, "WeekInCalendar", each "Week Number" & Number.ToText([Week Number]) & "Week - " & Number.ToText([Year])),

[FrankAT · Accepted solution]

Hi @Anonymous ,

use this M code:

InsertWeekInCal = Table.AddColumn(InsertWeekNumber, "WeekInCalendar", each "Week " & Number.ToText([Week Number]) & " - " & Number.ToText([Year]))

 With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

Hi @FrankAT    I have no doubt this code works, but the odd thing is that it does not show up as a new column when I click "save".    I don't get any syntax errors...the column just does not show up?

[Anonymous]

@FrankAT    Actually, it does work....but after the next step were I entered it in the advance editor, it breaks and I get a cyclic error.    I've tried moving that code up...and down...in the m code query and I can move it up earlier with no issue, but if I move it down past another step it gives me the error.   Any ideas how to correct that?  

InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date],1)),
InsertWeekInCal = Table.AddColumn(InsertWeekNumber, "WeekInCalendar", each "Week " & Number.ToText([Week Number]) & " - " &Number.ToText([Year])),
#"Changed Type" = Table.TransformColumnTypes(InsertWeekInCal,{{"WeekInCalendar", type text}}),
InsertMonthnYear = Table.AddColumn(AddedCustom3,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),

[Anonymous]

All good now!   I just placed that new code at the bottom of my existing code and things worked fine.   I've marked your answer as the solution.  

Thank you