Forum Discussion

Matas's avatar
Matas
Icon for Advocate II rankAdvocate II
4 years ago
Solved

SSIS Derived Column Syntax

Hi,   I have an issue and cannot find easy solution. I have a data coming as a TimeStamp in a weird format (this format cannot be changed) as "20.190.105.070.515" it indicates yyyy-mm-dd-hh-mm-ss. ...
data protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Matas ;

    1. Change "." to ""

    2.You could create a column in Power BI desktop :

    Column = DATE(LEFT([TimeStamp],4),MID([TimeStamp],5,2),MID([TimeStamp],7,2))

     The final output is shown below:

    Or create a custom column in power query .

    = Table.AddColumn(#"Replaced Value", "Custom", each Text.Start([TimeStamp],4)&"-"&Text.Middle([TimeStamp],4,2)&"-"&Text.Middle([TimeStamp],6,2))

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi, Matas ;

1. Change "." to ""

2.You could create a column in Power BI desktop :

Column = DATE(LEFT([TimeStamp],4),MID([TimeStamp],5,2),MID([TimeStamp],7,2))

 The final output is shown below:

Or create a custom column in power query .

= Table.AddColumn(#"Replaced Value", "Custom", each Text.Start([TimeStamp],4)&"-"&Text.Middle([TimeStamp],4,2)&"-"&Text.Middle([TimeStamp],6,2))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.