Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Separate Date and Time From Timestamp in Calculated Column

I apologize in advance if this question has been aked before, but I've looked around and can't find the anwser that quite addresses my issue... I have a table in a tabula model with several datetime...
Need Help
PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

Hi Anonymous 

OK so going back to PQ, if you start with this

Source = #"SQL/azsds001d database windows net;EDW",
ops_DimFlightSchedule = Source{[Schema="ops",Item="DimFlightSchedule"]}[Data],
#"Filtered Rows" = Table.SelectRows(ops_DimFlightSchedule, each ([Cancelled] <> 2))
in
#"Filtered Rows"

then change it to this

let
   Source = #"SQL/azsds001d database windows net;EDW",
   ops_DimFlightSchedule = Source{[Schema="ops",Item="DimFlightSchedule"]}[Data],
   #"Filtered Rows" = Table.SelectRows(ops_DimFlightSchedule, each ([Cancelled] <> 2)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "DepartureDateTime", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"DepartureDateTime.1", "DepartureDateTime.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DepartureDateTime.1", type text}, {"DepartureDateTime.2", type time}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"DepartureDateTime.1", type date}}, "en-US"),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"DepartureDateTime.1", "DateDeparture"}, {"DepartureDateTime.2", "TimeDeparture"}})
in
    #"Renamed Columns"

To give this

Note that my date format is d/m/y 

If you want to repeat these steps in another query then copy/paste everything after the #"Filtered Rows" line

Phil

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks again for the reply! So, I went through the process of updating my PowerQuery query in Visual Studio and have this now:

     

     ops_DimFlightSchedule1 = Source{[Schema="ops",Item="DimFlightSchedule"]}[Data],
    #"Filtered Rows" = Table.SelectRows(ops_DimFlightSchedule1, each ([Cancelled] <> 2)),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "ScheduleDepartureDatetimeZ", "ScheduleDepartureDatetimeZ - Copy"),
    #"Extracted Time" = Table.TransformColumns(#"Duplicated Column",{{"ScheduleDepartureDatetimeZ - Copy", DateTime.Time, type time}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Time",{{"ScheduleDepartureDatetimeZ - Copy", "ScheduleDepartureTimeZ"}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "ScheduleDepartureDatetimeLocal", "ScheduleDepartureDatetimeLocal - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column1",{{"ScheduleDepartureDatetimeLocal - Copy", "ScheduleDepartureTimeLocal"}}),
    #"Extracted Time1" = Table.TransformColumns(#"Renamed Columns1",{{"ScheduleDepartureTimeLocal", DateTime.Time, type time}}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Extracted Time1", "OutZ", "OutZ - Copy"),
    #"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column2",{{"OutZ - Copy", "ActualDepartureTimeZ"}}),
    #"Extracted Time2" = Table.TransformColumns(#"Renamed Columns2",{{"ActualDepartureTimeZ", DateTime.Time, type time}}),
    #"Duplicated Column3" = Table.DuplicateColumn(#"Extracted Time2", "OutLocal", "OutLocal - Copy"),
    #"Renamed Columns3" = Table.RenameColumns(#"Duplicated Column3",{{"OutLocal - Copy", "ActualDepartureTimeLocal"}}),
    #"Extracted Time3" = Table.TransformColumns(#"Renamed Columns3",{{"ActualDepartureTimeLocal", DateTime.Time, type time}}),
    #"Duplicated Column4" = Table.DuplicateColumn(#"Extracted Time3", "OutZ", "OutZ - Copy"),
    #"Renamed Columns4" = Table.RenameColumns(#"Duplicated Column4",{{"OutZ - Copy", "ActualDepartureDateZ"}}),
    #"Extracted Date" = Table.TransformColumns(#"Renamed Columns4",{{"ActualDepartureDateZ", DateTime.Date, type date}}),
    #"Duplicated Column5" = Table.DuplicateColumn(#"Extracted Date", "OutLocal", "OutLocal - Copy"),
    #"Renamed Columns5" = Table.RenameColumns(#"Duplicated Column5",{{"OutLocal - Copy", "ActualDepartureDateLocal"}}),
    #"Extracted Date1" = Table.TransformColumns(#"Renamed Columns5",{{"ActualDepartureDateLocal", DateTime.Date, type date}}),
    #"Duplicated Column6" = Table.DuplicateColumn(#"Extracted Date1", "InZ", "InZ - Copy"),
    #"Renamed Columns6" = Table.RenameColumns(#"Duplicated Column6",{{"InZ - Copy", "ActualArrivalDateZ"}}),
    #"Extracted Date2" = Table.TransformColumns(#"Renamed Columns6",{{"ActualArrivalDateZ", DateTime.Date, type date}}),
    #"Duplicated Column7" = Table.DuplicateColumn(#"Extracted Date2", "InLocal", "InLocal - Copy"),
    #"Renamed Columns7" = Table.RenameColumns(#"Duplicated Column7",{{"InLocal - Copy", "ActualArrivalDateLocal"}}),
    #"Extracted Date3" = Table.TransformColumns(#"Renamed Columns7",{{"ActualArrivalDateLocal", DateTime.Date, type date}}),
    #"Duplicated Column8" = Table.DuplicateColumn(#"Extracted Date3", "InZ", "InZ - Copy"),
    #"Renamed Columns8" = Table.RenameColumns(#"Duplicated Column8",{{"InZ - Copy", "ActualArrivalTimeZ"}}),
    #"Extracted Time4" = Table.TransformColumns(#"Renamed Columns8",{{"ActualArrivalTimeZ", DateTime.Time, type time}}),
    #"Duplicated Column9" = Table.DuplicateColumn(#"Extracted Time4", "InLocal", "InLocal - Copy"),
    #"Renamed Columns9" = Table.RenameColumns(#"Duplicated Column9",{{"InLocal - Copy", "ActualArrivalTimeLocal"}}),
    #"Extracted Time5" = Table.TransformColumns(#"Renamed Columns9",{{"ActualArrivalTimeLocal", DateTime.Time, type time}})
in
    #"Extracted Time5"

     When viewing the new table in PowerQuery, the columns display as expected:

    I create a table in te Tabular model using this same query. When I view the data in the tabular model, however, the date portion is still present - even though I format the column to JUST have the time:

     

    The problem is that this column is used in a relationship with a Time Dimension table. Since the actual value isn't just the time, the relationship doesn't work.

    Getting closer!!