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
RicLup
Helper III
Helper III

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:

 

Capture6.PNG

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!!

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

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:

e1.png

 

Calculated column:

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

 

Result:

e2.png

 

Best Regards

Allan

 

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

View solution in original post

8 REPLIES 8
v-alq-msft
Community Support
Community Support

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:

e1.png

 

Calculated column:

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

 

Result:

e2.png

 

Best Regards

Allan

 

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

thanks a lot @v-alq-msft works great too!!

Ashish_Mathur
Super User
Super User

Hi,

Why in the first place do you want to do this?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mussaenda
Super User
Super User

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"

 

2020_08_29_08_55_48_Untitled_Power_Query_Editor.png

 

Hope this helps!

Greg_Deckler
Super User
Super User

@RicLup - If that column is text. I'm not sure what you are trying to do though exactly


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!

@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]&"")

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Tahnks a lot @Greg_Deckler  , works great!

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.