Forum Discussion

Chanleakna123's avatar
Chanleakna123
Post Prodigy
5 years ago

How to Convert Number to Date

hi all ,    i have around 300 Excel File to be imported as Folder in Power BI ,  In each excel those are in date Format , when i import into Power BI , it turns out to Number instead , and i tryna...
Show and Tell
PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

Hi Chanleakna123 

44100 is the Excel serial number for the date Sep 26 2020. In your worksheet the date is entered as a date in m/d/y format.

If you are also using m/d/y format it should automatically be recognised as a date.  But you can see from the top of the Date columns that they are set as any (abc 123) type.

PQ has probably done an automatic type conversion as the 2nd step after importing, perhaps because you are not using d/m/y format.  Delete this and set the date properly using Change Type -> Using Locale or just by clicking on the abc 123 and then clicking Date.

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

  • Chanleakna123's avatar
    Chanleakna123
    Post Prodigy
    5 years ago

    Hi , amitchandak  PhilipTreacy , my

    file imported in BI , appeared to hve some row Sync  as date and some sync as serial number. 
    omce I convert to date , the serial might be error. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Chanleakna123,

      Perhaps you can try to add a new column on the 'query editor' side to store date value who transforms from the serial number, then you can use both of two type of values at the same time.

          #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each Date.AddDays(Date.FromText("1/1/1960"),Number.From ([Delivery Date] )))

      Regards,

      Xiaoxin Sheng