question

Ge-6375 avatar image
0 Votes"
Ge-6375 asked Ehren commented

Date column contains some data with 5 digital number

Hi

The original data file's Date column contains some data with 5 digital number like 44315. How to convert them into normal date format like 2021-09-13 in Power Query?

Thanks,

133365-image.png


power-query-not-supported
image.png (9.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RonRosenfeld-3452 avatar image
1 Vote"
RonRosenfeld-3452 answered RonRosenfeld-3452 edited

In Power Query, dates will be displayed according to your Windows Regional Settings short date format.

Since the original data appears to be "real dates" either formatted as yyyy-mm-dd or the unformatted `number of days since 1-Jan -1900, all you have to do in Power query is set the data type to type date:

 Table.TransformColumnTypes(Source,{
                 {"Date", type date})

Source
133747-image.png

M Code

 let
     Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
     #"Changed Type" = Table.TransformColumnTypes(Source,{"Date", type date})
 in
     #"Changed Type"

Results
133686-image.png





image.png (7.1 KiB)
image.png (4.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ge-6375 avatar image
0 Votes"
Ge-6375 answered Ehren commented

I can't understand this part :

Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content]

all data should be transformed into Power Query Editor, right? Why the source is Excel table?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You can ignore that if it doesn't apply in your scenario. The important part is the date conversion, which you can do via the UI.

0 Votes 0 ·