Forum Discussion

Christian2320's avatar
Christian2320
New Member
1 year ago
Solved

text to time

Hello! My first post here, thanks for reading! I have a PostgreSQL database with a table, which cointains schedule information of public transport. The hours of time of departure can be more than 2...
Need Help
Tips and Tricks
  • Hakuna_matata's avatar
    Hakuna_matata
    1 year ago

    I see you mentioned using Direct query, I would suggest using Native query

    1. Creating your model with the property "Direct Query Mode: Off".

    -Then, If you create your own query, using Sql.Database()

    let
    database name
    Source = Sql.Database("MyConnection", "DATABASE", [Query="
        SELECT
            departure_time,
            -- Convert departure_time (HH:MM) to total minutes
            CAST(split_part(departure_time, ':', 1) AS INTEGER) * 60 + CAST(split_part(departure_time, ':', 2) AS INTEGER) AS total_minutes
        FROM
            transport_schedule
    "]),
    // if needed
    CleanedData = Table.SelectRows(Source, each [total_minutes] > 0)  // Example of filtering
in
    CleanedData

     -Once transformation is done you may switch to "Direct Query Mode: On"

    That might work!

Hakuna_matata's avatar
Hakuna_matata
Resolver I
1 year ago

Hi Christian2320 ,

You may transform this in Power Query using Custom column formula:

let
hours = Number.FromText(Text.Start([DepartureTime], Text.PositionOf([DepartureTime], ":"))),
minutes = Number.FromText(Text.End([DepartureTime], Text.Length([DepartureTime]) - Text.PositionOf([DepartureTime], ":") - 1)),
totalMinutes = hours * 60 + minutes
in
totalMinutes

 

This will give you totals minutes ex.27:30 will become 1650 and make this column a numeric column, which you may now use for any calculations or time-based logic.

 

Other solution may be:

 

  • Splitting the column by ":" 

  • You can convert the split columns to numbers (hours and minutes).

  • You can optionally calculate total minutes to make it easier to work with time-based data.

Hope it helps!

If this solved your problem, please accept it as a solution!!

 

  • Christian2320's avatar
    Christian2320
    New Member
    1 year ago

    Thank you! I will give it a try. But custom columns often don't work in DirectQuery-Mode.

    • Hakuna_matata's avatar
      Hakuna_matata
      Resolver I
      1 year ago

      I see you mentioned using Direct query, I would suggest using Native query

      1. Creating your model with the property "Direct Query Mode: Off".

      -Then, If you create your own query, using Sql.Database()

      let
    database name
    Source = Sql.Database("MyConnection", "DATABASE", [Query="
        SELECT
            departure_time,
            -- Convert departure_time (HH:MM) to total minutes
            CAST(split_part(departure_time, ':', 1) AS INTEGER) * 60 + CAST(split_part(departure_time, ':', 2) AS INTEGER) AS total_minutes
        FROM
            transport_schedule
    "]),
    // if needed
    CleanedData = Table.SelectRows(Source, each [total_minutes] > 0)  // Example of filtering
in
    CleanedData

       -Once transformation is done you may switch to "Direct Query Mode: On"

      That might work!