Forum Discussion
Create YYYY-MM column
My suggestion above was to create a new DAX Column referencing your Date column
That would be on the Modeling Tab - New Column (NOT in the Query Editor)
YYYY-MM Column = VALUE ( FORMAT ( CalendarTable[Date], "YYYYMM" ) )
If you want this done with M in the Query Editor - Add Column tab - Custom Column
and use the columns Month and Year (which I assume you added using the From Date & Time option on the Add Column tab)
= Number.ToText([Year]) & (if [Month] < 10 then "0" else "") & Number.ToText([Month])
or if you want to refence the Date column again
= Number.ToText(Date.Year([Date])) & (if(Date.Month([Date])) < 10 then "0" else "") & Number.ToText(Date.Month([Date]))
there may be an easier way with M (like there is in DAX using FORMAT and VALUE with no IF statement)
Perhaps MarcelBeug can tell us?
However that should do it! :smileyhappy:
If not post a screenshot of what goes wrong and where?
My suggestion above was to create a new DAX Column referencing your Date column
That would be on the Modeling Tab - New Column (NOT in the Query Editor)
YYYY-MM Column = VALUE ( FORMAT ( CalendarTable[Date], "YYYYMM" ) )
If you want this done with M in the Query Editor - Add Column tab - Custom Column
and use the columns Month and Year (which I assume you added using the From Date & Time option on the Add Column tab)
= Number.ToText([Year]) & (if [Month] < 10 then "0" else "") & Number.ToText([Month])
or if you want to refence the Date column again
= Number.ToText(Date.Year([Date])) & (if(Date.Month([Date])) < 10 then "0" else "") & Number.ToText(Date.Month([Date]))
there may be an easier way with M (like there is in DAX using FORMAT and VALUE with no IF statement)
Perhaps MarcelBeug can tell us?
However that should do it! :smileyhappy:
If not post a screenshot of what goes wrong and where?
I know this is an old Post but hopefully someone might find this useful.
Using M in Power Query you can create the format YYYYMM by using the following
Table.AddColumn(#"Renamed Columns1", "Period Key", each Date.ToText([FullDate],"yyyyMM"))
This will add a 0 in front of the months 1-9 automatically. [Full Date] is just a date column