Forum Discussion
Converting Multiple Columns from Text to Date
Usually, dates from Excel are converted to date/time format when importing in Power Query.
If yours are converted to text, then it looks like Power Query is not able to identify the data as date.
Possibly your dates are in columns that also contain non-date values?
Are you importing your data directly from worksheets or is your data organized in Excel-tables?
Should dates be needed to convert from text to dates in Power Query, then it is imported to know the date format and the culture (or Locale) you are using.
Thank you Marcel for reverting back.
The data is organized in Excel worksheet and I am importing that in Power BI. Since multiple users enter the data in that excel daily, it is not feasible to make all the date columns in date format and there are more than 240 columns for date values. Either I sit and convert those columns manually in date format in excel, which is very time consuming. Or I import all data in Power BI and run a script for conversion. The issue is Power BI is not able to convert such volume of columns to date.
Do you have any thoughts how it can be handled instead of manual effort to change formats in excel sheet ?
Thanks,
Isha.
Your question is still not clear, but when I try to load an Excel sheet with 500 columns of dates (between date values 40,000 and 50,000) and 20 rows each, Power BI doesn't like the option "Auto Date/Time", because it creates a background table for each date or datetime field.
If I uncheck that option, there is no problem.
If you have another issue, then please clarify and enable us to help you properly.
Possibly provide some fake exampe data so we can paint the picture.