Forum Discussion

Isha's avatar
Isha
Frequent Visitor
9 years ago

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 ...
Need Help
Isha's avatar
Isha
Frequent Visitor
9 years ago

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.

 

 

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

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.