Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ...
Need Help
  • FrankAT's avatar
    FrankAT
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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