Forum Discussion

RicLup's avatar
RicLup
Helper III
6 years ago
Solved

Combine Date formats in the same Column

Hi all

 

Hoping you can help me, if is possible combine date format in a column? I need to have a column like this in the expected date field:

 

As you can see i need to convert the actual date field with values = 01/01/YYYY for each year, because i have a total sales amount for month and year. I need to show the totals in a table with the format mentioned in the table above.

 

Regards and thanks a lot!!

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, RicLup 

     

    In Power BI Desktop, a column can only have a data type. If you want to have a column with two format("yyyy", "mm/dd/yyyy"). I'd like to suggest you try the following calculated column to get the result with text data type. I created data to reproduce your scenario.

    Table:

     

    Calculated column:

    Formatted Date = 
IF(
    MONTH([Actual Date])=1&&DAY([Actual Date])=1,
    FORMAT([Actual Date],"yyyy"),
    FORMAT([Actual Date],"mm/dd/yyyy")
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion
    6 years ago

    Hi RicLup,

     

    I have assumed that it is okay on your side to convert your dates into string.

    I did this in M Query.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc2xDcBACEPRXahPwpg0mQWx/xoJxSW+8uthUWUZjnCCsKXRq4y3g9skxuY0dZdicFyf/TGG/SJekziMajwt1dK6Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Actual Date" = _t, #"Expected Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Actual Date", type date}, {"Expected Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains(Date.ToText([Expected Date]), "2020")
then [Expected Date]
else Text.AfterDelimiter(Date.ToText([Expected Date]), "/",{0, RelativePosition.FromEnd}))
in
    #"Added Custom"

     

     

    Hope this helps!

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, RicLup 

     

    In Power BI Desktop, a column can only have a data type. If you want to have a column with two format("yyyy", "mm/dd/yyyy"). I'd like to suggest you try the following calculated column to get the result with text data type. I created data to reproduce your scenario.

    Table:

     

    Calculated column:

    Formatted Date = 
IF(
    MONTH([Actual Date])=1&&DAY([Actual Date])=1,
    FORMAT([Actual Date],"yyyy"),
    FORMAT([Actual Date],"mm/dd/yyyy")
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • RicLup's avatar
      RicLup
      Helper III
      6 years ago

      Hello @Greg_Deckler

      Thanks for the tracking, the actual date of the column is the date type, so I can't combine the values that change the format of the types,

      I can only use one option for all values, for example:

      if I apply the year date option all values change to year:

      Capture7.PNG

      This is the result;

      Capture8.PNG

      So I need the result like this in date format:

      Capture9.PNG

      Thanks a lot!!

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        RicLup - Right, I guess what I was saying or trying to say was to in your Power Query don't convert it to a date and instead convert it to text (I'm not sure how it is represented in your source data) or create a new column like:

        Expected Date = IF(DAY([Actual Date])=1&&MONTH([Actual Date])=1,YEAR([Actual Date])&"",[Actual Date]&"")