cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Converting Multiple Columns from Text to Date


I am using the excel sheet with 240 columns holding date values. When I getting data from this sheet into Power BI, the date columns are loaded as text. I ran a script converting all these columns to Date format. Because of the high volume of conversion, Power BI is unable to apply this change. Any solution or workaround ?
 
Thanks

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: Converting Multiple Columns from Text to Date


@Isha wrote:


I am using the excel sheet with 240 columns holding date values. When I getting data from this sheet into Power BI, the date columns are loaded as text. I ran a script converting all these columns to Date format. Because of the high volume of conversion, Power BI is unable to apply this change. Any solution or workaround ?
 
Thanks


@Isha

Are those 240 columns all used in your report? Try to filter and select the columns that only needed and try to do the conversion.

Highlighted
Community Champion
Community Champion

Re: 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.

Specializing in Power Query Formula Language (M)
Highlighted
Frequent Visitor

Re: Converting Multiple Columns from Text to Date

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.

 

 

Highlighted
Frequent Visitor

Re: Converting Multiple Columns from Text to Date

Yes Eric, all 240 columns are used and the list will keep growing, I unpivot data after bringing in Power BI to visualize it though. But after that if I need to plot timeseries based data into a visual, which needs the date columns in date format rather text.

 

Regards,

Isha.

Highlighted
Community Champion
Community Champion

Re: Converting Multiple Columns from Text to Date

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.

 

Auto datetime.png

 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors