Convert 5-digits to yyyy/mm/dd

[machi0604]

Hello!

I'm wondering how I can convert 5-digit number to date type. I'm new to using PowerBI and utilizing an excel data so that would be nice if I can get some help from you guys!

So I have 5 digits, for example 43344 (which is called serial number, according to my research) and that can be converted as 2022/09/02 on excel. But if I just change the data type on PowerBI, 43344 would be 2018/09/01 and it's not the number I want. 

Does PowerBI even know the concept of serial number? 

I know I can change the data type on excel and then import it on PowerBI but I don't really want to do anything to my raw data.

Please help 😞 Thank you!

[eliasayyy]

hello im not sure how you are getting in excel 43344 as 9/2/2022 itshould give you 

9/1/2018

[Kishore_KVN]

Hello @machi0604 ,

The serial number that you mentioned is also showing same date in excel. What I mean is 43344 is converted as 01-09-2018 in excel as well please refer below screenshot:

For your information, formula that I used to convert in excel is mentioned below:

=DATE(YEAR(A1),MONTH(A1),DAY(A1))

same you can use in Power BI as well and the output will be the same. 

Please check how you got 2022/09/02 in excel. 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[Decal]

You would do the same in Power BI 
DATE = DATE(
YEAR(column where you have the 5 digits),

MONTH(column where you have the 5 digits),
DAY(column where you have the 5 digits)

)

[Idrissshatila]

Hello @machi0604 ,

What I would tell you that, it's better to perform the transformation on the data source than to do it in Power BI.

Also as you said, Power BI Would recognise it as 2018/09/01, so prepare it right in Excel then import the data prepared.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin