Forum Discussion

alvin199's avatar
alvin199
Helper III
4 years ago

Problem on create date column

Hi, my raw data does not have a date date type column. It only label the excel sheets with quarter-year. After imported all 4 sheets of excel I created 2 new calculated column to fill up all row with...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

alvin199 I'm confused by what you're trying to achieve.  In order for a field to be in Date data type, the value needs to be an actual date.  Not a quarter, not a year, a date.

So what is the actual date that you woiuld like represented in your "Date" column?  Perhaps the first day of the Quarter?  In which case you could do something like below:

 

Start of Quarter Date = 
DATE(RIGHT('Table'[QuarterYear],4), MAX(((RIGHT(LEFT('Table'[QuarterYear],2),1))*3)-3,1),1)
  • alvin199's avatar
    alvin199
    Helper III
    4 years ago

    Anonymous 

     

    Thanks for the reply. 

    The Quarter and Year are the columns that I added in Power Query. The original data do not have these 2 columns. It is a label on the original data. Here is how it looks like.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      alvin199 again, what is the date you are actually trying to represent? The start of the quarter?  The end of the quarter?  Some random date in the middle of the quarter?  

      • alvin199's avatar
        alvin199
        Helper III
        4 years ago

        Anonymous 

         

        The dataset has labelled Quarter 1 2021 until Quarter 4 2021 but it does not have a column putting Quarter and year and date type data. So, I created two calculated columns, namely Quarter (for 1 to 4) and Year (2021). The data type is text. If I change to date or date/time data type, it will give out Error in Power Query.