Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 change to Date Format , but it turns out still error.
the same error message appear.
may i know how to settle this ?
i don't expect to change all of the Date to other format . becuase i have 300 Excel file which are the same format .
@Chanleakna123 , One of the two should work
Delivery Date New = date(1970,01,01) +[Delivery Date]
or
Delivery Date New = date(1900,01,01) +[Delivery Date]
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.
Proud to be a Super User!
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |