cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tylerdv
Frequent Visitor

Create YYYY-MM column

Hi Guys

 

I am trying to create my monthly reporting where i can show the results for the last 12 months (eg April 16 to April 17

 

I am having issues sorting the data in chronological order.

 

Using the 'Add Month #' and 'add year' transforms i have managed to get them in, but when merging then using the sort column function. September 2016 (20169) is placed after Octomber 2016 (201610) due to Power BI reading 20161 as lower than 20169)

 

How can i create a column that will place a 0 in the YYYYMM format so i can have 201609 which should read correctly in Power BI

 

Thanks Heaps

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@tylerdv

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! Smiley Happy

 

If not post a screenshot of what goes wrong and where?

View solution in original post

8 REPLIES 8
Sean
Community Champion
Community Champion

@tylerdv

The easiest way would be to reference your Date Column like this Smiley Happy

YYYY-MM Column = VALUE ( FORMAT ( 'Calendar'[Date], "YYYYMM" ) )

 

tylerdv
Frequent Visitor

Hi @Sean

 

Thanks for the response, when doing that the values come out as decimal numbers 

 

42614

62644

62675 etc

When formating them to date it includes the dd-mm-yyyy again

 

KHorseman
Community Champion
Community Champion

@tylerdvno he means you should create a new column using that formula. Then you can use that column as a sort value for your month column using the Sort By Other Column button.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Is there a link shwoing me how to do this properly? 

 

Can work it out sorry.

Sean
Community Champion
Community Champion

@tylerdv

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! Smiley Happy

 

If not post a screenshot of what goes wrong and where?

View solution in original post

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

tylerdv
Frequent Visitor

@Sean

 

Query Editor custom column works a treat!


Thanks heaps

 

MarcelBeug
Community Champion
Community Champion

@Sean, @tylerdv

 

My suggestion would be

 

= Text.From(100*[Year]+[Month])

or

 

= Text.From(100*Date.Year([Date])+Date.Month([Date]))

 

Alternatively you can omit the Text.From. 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!