Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!
Solved! Go to Solution.
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.
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.
Hi,
Why in the first place do you want to do this?
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!
@RicLup - If that column is text. I'm not sure what you are trying to do though exactly
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:
This is the result;
So I need the result like this in date format:
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]&"")
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |