Newbie: Advice on Formatting a Date Column (from a data source)

[jimmyg706]

Evening Folks

Im having some trouble with a date column. It comes into to PBI in the following format:   2023-08-24T15:41:13Z

Now I want a dd/mm/yy format with no time so above would be 24/08/2023

Unfortunately I cant just change it to Date (Transform)  as it says error for all the records if I transform and change the type to dd/mm/yy

So I normally select the column and Add Column With Examples

In PBI you are then prompted to put in an example but it always has the time extension after see below

If I just go with the flow I to get a new date column that looks like dd/mm/yy however when I tried to some DAX calculations on it – I get this error message:

Cannot convert value '2023-08-24T00:00:00Z' of type Text to type Date.

In my Table   2023-08-24T00:00:00Z' looks like 24/08/2023 and is formatted as Date but it clearly isn’t.

Question:  What method would use to get to my end game which is a date only column?

Fig 1 – Original Column – If I try and change to date (transform) I get errors

Fig 2

After Creating Column with examples I have the following and notice it doesn’t look like a Date column

Data View  - Table View – It all looks like expected.

DAX Calculated Column and notice the error:  Cannot convert value '2023-10-09T00:00:00Z' of type Text to type Date.

If anyone can suggest a better way  it would be appreciated.

Jimmy

[jimmyg706]

@ChiragGarg2512   Indeed the parse worked in that I can now get my formulsd to work with DATEDIF which was the main challenge.

It still doesnt see it as a Date field but I will persevere on this one. 

So a big thanks for solving.

Jimmy

[ChiragGarg2512 · Accepted solution]

@jimmyg706 , Click on the Task Completed At column, then go to Transform->Date->Parse.
Remember to select the column.

[ChiragGarg2512]

In Power Query, on right clicking the column header there is an option to change the format from date/time to just date.

Try this and if the problem continues, send some data in tabular format.

[jimmyg706]

Thanks @ChiragGarg2512 

Indeed I tried that.

Ive pasted some data below

Task Completed At
2023-08-21T06:23:35Z
2023-08-21T06:23:36Z
2023-08-21T06:24:40Z
2023-08-21T06:24:40Z
2023-08-21T06:24:41Z
2023-08-22T09:12:31Z
2023-09-01T18:32:25Z
2023-09-08T09:56:38Z
2023-09-08T09:56:38Z
2023-09-08T09:56:38Z
2023-09-08T09:56:39Z
2023-09-08T09:56:39Z
2023-09-14T10:41:23Z