Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Isha
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
Eric_Zhang
Employee
Employee


@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.

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.

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)

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
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.