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 24 hours, e.g. 27:30.  So I decided to use the datatype "text" in Postgres because Power BI can't handle the datatype interval.
Now I have to convert this text-column hh:mm to time in a way that DirectQuery-Mode still works. The recommendations of Copilot did not work.

Any suggestions?

 

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!

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Christian2320,

    Thanks for reaching out to the Microsoft fabric community forum.

    It looks like you are looking for a way to convert text type column to data type column. As Hakuna_matata and Akash_Varuna both already responded to your query, can you please verify if you were able to solve your issue or went through their responses.

     

    I would also take a moment to thank Hakuna_matata and Akash_Varuna, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

    Best Regards,
    Hammad.
    Community Support Team

     

    If this post helps then please mark it as a solution, so that other members find it more quickly.

    Thank you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Christian2320,

      As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

      If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.


      If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
      Thank you for your patience and look forward to hearing from you.

  • 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!

  • Christian2320's avatar
    Christian2320
    New Member
    1 year ago

    Sorry for coming back so late! In the meantime I've got a new collegue (data analys) who solved the problem. He splits time into three columns for hours, minutes and seconds when importing the data into the database. Works.
    Thank you!