Forum Discussion
M code question
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])),
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)
4 Replies
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)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?
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),