Forum Discussion
Data transformation challenge - Jira data source
Hi all , my data source is Jira and one of the columns I require is the "Last Comment Date" which is a text column which I would like to be in date format.
The challenge being that this text column has different formats depending on whether there is data in another column, "Last Comment", as below.
If there is not data in the Jira column "Last Comment" then the "Last comment date" is set as "2023-04-14 08:25:21.0" whereas if there is a text value in the "Last Comment" column then the "Last comment date" is set as "Fri Apr 14 08:25:21 UTC 2023" or what ever the date was when the "Last Comment date" was made.
It transpires that if there is no "Last comment" the "Last Comment Date" appears to be set to the same as the jira column created date which is presented as 14/04/2023.
Any ideas most welcome on how I can transform this data so as to be able to use the "Last comment date" as a date.
In the end I added a custom column using the following
= Table.AddColumn(#"Renamed Columns35", "LastCommentDateModified", each let
a = Text.BeforeDelimiter([LastCommentDate], " "),
b = Date.FromText(a, [Format="yyyy-MM-dd"]),
c = Text.BetweenDelimiters([LastCommentDate], " ", " ", 0 ,1),
d = Text.End([LastCommentDate], 4),
e = try b otherwise Date.FromText(c&d, [Format="MMM ddyyyy"])
in e)
2 Replies
You use the Text functions to build a string that can be passed to a date parser within power query https://learn.microsoft.com/en-us/powerquery-m/text-functions#extraction
The date parser functions can be used by typing them in or via the interface on the Transform menu.
--
The first on looks like you can take everything before the .0 and see if that will parse as a datetime. If you don't need the time, just use Text.Start to get the first characters and make a date.
The 2nd one looks like a combination of Text.Start and Text.End will get you Fri Apr 14 2023 and you can try to parse that as a date
----
Test these separately with custom columns and, when you get it working, you can use an 'if' statement to parse each one. The 'if' will test if there is text in the 'last comment' field.
In the end I added a custom column using the following
= Table.AddColumn(#"Renamed Columns35", "LastCommentDateModified", each let
a = Text.BeforeDelimiter([LastCommentDate], " "),
b = Date.FromText(a, [Format="yyyy-MM-dd"]),
c = Text.BetweenDelimiters([LastCommentDate], " ", " ", 0 ,1),
d = Text.End([LastCommentDate], 4),
e = try b otherwise Date.FromText(c&d, [Format="MMM ddyyyy"])
in e)
