Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 colu...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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)

     

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
2 years ago

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.