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 to Date format. Because of the high volume of conversion, Power BI is unable to apply this change. Any solution or workaround ?
 
Thanks

Need Help

5 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago
    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.

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      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.

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

         

         

    • Isha's avatar
      Isha
      Frequent Visitor
      9 years ago

      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.