cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Changeing data type from whole number to date (year and month)

Hi
I loaded data with following format:
201201
201202
201203 and so on
Data above is presented in column showing year and month (for example 2012 january). 
Unfortunately, Power BI recognize only whole numbers and when I try to change this data type to date, I get very strange numbers (like 2452-07-04 or similar).
Any idea how I can convert those numbers (whole numbers) to date (Year and month - 201201)

Thanks in advance. Whole numbers.JPGDate Time.JPG

 

 

1 ACCEPTED SOLUTION

Hello @DanielPasalic 

 

my other solution would transform you period-column into a dateformat, using this logic. Mabye it's better to keep the period-column and add a new one. Exactly, when you load the new data, this column is always created and you can use it in your datamodel.

Maybe change the type of the column immediatley after into a date-format


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

15 REPLIES 15
Super User III
Super User III

Hello @DanielPasalic 

 

here another way to solve this issue. You have to transform the number to text and add 01 for the first day, and then convert to date.

 

let
	Source = #table
	(
		{"Period"},
		{
			{"201810"},	{"201910"},	{"202010"},	{"201901"}
		}
	),
    ChangeType = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}}),
	ToDate = Table.TransformColumns
	(
		Source,
		{
			{
				"Period",
				each Date.From(Text.From(_)&"01", "de-DE"),
				type date
			}
		}
	)
in
	ToDate

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Maybe I should explain my problem a little more detailed in order to find the best solution. 
I need to find solution with following problems:
1. Transform existing column with whole numbers (201201 and similar) to Date column consisting of Year and Month
2. Creating Date Table (in Edit Queries or Data model using DAX) in order to creat relationship
3. Find solution for upcoming data so the period column consisting of 6 numbers (202001) automatically convert to Date consisting Year and month - 2020-01 (option is also 2020-01-01 - using only 1 day in the month) 

 

Problem description 
Every month my Power Bi Query download thousands of rows with sale statistics as the table - Data_ArtDisk_CSV (please see the photo) is connected to my folder where I place those reports. 
Every report consists of raw with name Period - which is connected to the year and month this data is for. 
So far I have data from 201212 to 201912 (from December 2012 to December 2019) for each month. Each period consist thousands of raws with sale statistic for different items sold that particular year/month. 
The main problem is that Period raw consist only of year and month (for example 201212) and Power BI does not recognize as Date but as the whole number. 
As I would like to create Date Table and be able to create a relationship (one to many) to my report, I need t to have a recognizable column in my Data_ArtDisk_CSV. Today, 

Another problem is that new data is coming next month consisting of the same format (for example 202001). 
So what would be the best option and should I do it in Edit Queries or in Data view (make new column in Data_ArtDis) so that new column recognize all upcoming data in column "Period" and automatically convert to date - Year and month in the next column so I can creat relationship between date table and Data_ArtDisk_CSV. 
I'm also wondering if I shell create Date Table in Edit Queries or in Data Model?
Thanks a lot for your help, highly appreciated. Data whole numbers.JPGRelationships.JPG

Hello @DanielPasalic 

 

a real date column can consist only of year, month and day. Otherwise it would be a text. 

So my solution should fit, as I'm converting in a date and using the first day of month. 

To create a List of Dates you can use the function List.Dates(date, ripetions, duration) like List.Dates(#date(2020,1,1), 365, #duration(1,0,0,0)).

With this both on hand you should be able to solve your problems

 

All the best

 

Jimmy

 

I have a huge problem with formating my whole number column  to a date column. 
I get reports for sale and in sale period it is stated only year and month (201901, 201902 etc..) which is why Power BI does not recognise this column as Date but as a text or as a whole numbers.  
To get this simplified, I created in Queries 1 column with 0 and another with 1 and simply merged columns together and get values like:
20190101, 20190201, 20190301). After that, Power BI recognized this column as a date and I thought everything was great, 

However, I increased the size of data in my table. 
When I started to create New Measures in order to get the sales for Year, Month (creat YTD column, and sale LY) i realized that something is wrong. 
I started to create New Measure  Date_Year based on following: Date_Year = YEAR(Data_ArtDisk_CSV[Period]) / this is where my Period column containing all dates is. 
I get all years only (2019,2018,207 etc) but as Data Type and Format recognized as the Whole Number. When I would like to change to date I get totally different numbers (please photo below). 
Same things happened even with months, 
Date_Month = MONTH(Data_ArtDisk_CSV[Period]) / here I get only numbers from 1-12 but as the Photo0.JPGPhoto1.JPGPhoto2.JPGPhoto3.JPGPhoto4.JPGPhoto5.JPGwhole number and when I change to date, Data Type, I get totally strange numbers. 
I'm totally stuck with this and don't know what to do. 

Hello @DanielPasalic 

 

don't understand why you converto this dateformate YYYYMM to a real date as I suggested in the first place. Nevertheless, when you extract the month number (meanging this are numbers from 1 to 12) to a date, what do you expect to happen?

Power Pivot interprets this a day and so it's the first day of the counting range.. therefore you get this strange date in 1899.

 

Hope this helps

 

Jimmy

Sorry Jimmy
I really don't get your explanation. 
Possible to explain step by step, how I can do that because I'm total beginner for Power Bi

Hello @DanielPasalic 

 

as a first step, transform your Period into e real date. Add a custom column called "Date" and insert this formula

Date.From(Text.From([Period])&"01", "de-DE"),

this created a Date-Column with the first day of your month. When you have this new column in your data model, you can apply every YEAR, MONTH function in DAX.
Please let me know if you have further requirements then this

 

BR

 

Jimmy

 

Hi @Jimmy801 

I really don't get it. 
I done exactly as you told me and get this Problem.JPG

sorry... delete the "," at the end 🙂

Hi @Jimmy801 

Looks great! 
What about your previous suggestion as I creat the table with date according to your suggestion below.
I can delete that one?
Can I also delete the column named Period and just keep the date?

Datum.JPG

 

 

let
	Source = #table
	(
		{"Period"},
		{
			{"201810"},	{"201910"},	{"202010"},	{"201901"}
		}
	),
    ChangeType = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}}),
	ToDate = Table.TransformColumns
	(
		Source,
		{
			{
				"Period",
				each Date.From(Text.From(_)&"01", "de-DE"),
				type date
			}
		}
	)
in
	ToDate

 

@Jimmy801 

And last question...
Next time when I download data that has same problem (period - 202001). 
Shell I changed already in a file (excel file) or it will change automatically in the query?

 

thanks a lot

Hello @DanielPasalic 

 

my other solution would transform you period-column into a dateformat, using this logic. Mabye it's better to keep the period-column and add a new one. Exactly, when you load the new data, this column is always created and you can use it in your datamodel.

Maybe change the type of the column immediatley after into a date-format


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

Hi Jimmy
I managed to creat a new quary with your proposal. 
However, how I can apply this to existing table? 
Don't know where to paste in advance editor. 
At the end of existing text?

I get this message: Expression.SyntaxError: Token Eof expected.

 

 

 

let
Source = #table
(
{"Period"},
{
{"201212"},{"201312"},{"201412"},{"201512"},{201612},{"201712"},{"201812"},{"201912"}, {"201801"}, {201802},{"201803"},{"201804"},
{"201805"}, {"201806"}, {201807},{"201808"},{"201809"}, {"201810"}, {"201811"}, {201901},{"201902"}, {"201903"}, {"201904"}, {"201905"}, {201906}
,{"201908"}, {"201909"}, {"201910"}, {"201911"},{"201907"}
}
),
ChangeType = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}}),
ToDate = Table.TransformColumns
(
Source,
{
{
"Period",
each Date.From(Text.From(_)&"01", "de-DE"),
type date
}
}
),
#"Sorted Rows" = Table.Sort(ToDate,{{"Period", Order.Ascending}})
in
#"Sorted Rows"

Microsoft
Microsoft

Sure... just add a new custom column:

 

= #date(Number.IntegerDivide([Period], 100), Number.Mod([Period], 100), 1)

 

P.S. the syntax for the date is: #date(year, month, day) 

Can I change somehow the syntax for date to year an month only as my data has multiple rows with years and month sale, without days?

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors