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.
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])),
Solved! Go to 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)
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),
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
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.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |