Obtain proper date formatted column from 2 separate columns

[Anonymous]

I have this situation, where 2 columns contain the data on the datetime an occurence started. However, they are split so that the first gives me the datetime but has an empty time, and the second gives me the time but due to it not being attached to a date reads it as alphanumerical. How can I obtain the date time together? 

[HotChilli · Accepted solution]

Sql Server is a structured data source and I'm pretty sure Power Query reads the types from the sql table definitions.  That means that the TimeEndOperation is stored as a number type of some sort in Sql.  Maybe you can check this in the source and post it ?  Maybe I'm wrong and there is a specific implementation of Time in the Sql definition (TIME(1) or TIME(2))

If it is stored as a number there will be an algorithm (maybe in a stored procedure or the application code that uses the database) that tells us how to convert, for example, number of seconds since midnight. 

So we need to find out 2 things (sql data type and any conversion algorithm) 

[Anonymous]

Thank you for helping out, it was indeed a matter of wrong formatting. So, when I tried to manually input the column, numbers with 6 digits like 115527 would be properly changed to 11:55:27 time format, but not the numbers like 72316. This ndicated it should have been a time like 07:23:16, so I changed the type to text, made a conditional column which would add a "0" in front of each 5 digit value, and when done all the values properly changed to hour

[HotChilli]

What's the source?

Are there any transforms(steps) in Power Query that changed the type? (Maybe you can post the Advanced Editor code?)

[Anonymous]

Hey, no transformations. It comes like this directly from the db. Here is the Advanced Editor code:

let
    Origine = Sql.Database(SERVER, DATABASE),
    #"FilteredRows" = Table.SelectRows(Origine, each Text.StartsWith([Name], "BIT")),
    dbo_BIT_ANALISI_OPERATORI = #"FilteredRows"{[Schema="dbo",Item="BIT_ANALISI_OPERATORI"]}[Data],
    #"RenamedColumns" = Table.RenameColumns(dbo_BIT_ANALISI_OPERATORI,{{"NumDocumento", "Nr. ODS"}, {"OperatoreTermPort", "Nome Operatore"}}),
    #"ChangedType" = Table.TransformColumnTypes(#"RenamedColumns",{{"IdDocEsportato", type text}}),
    #"RenamedColumns1" = Table.RenameColumns(#"ChangedType",{{"IdDocEsportato", "ID_ODS"}})
in
    #"RenamedColumns1"

I know it's not entirely in English, do let me know if you need to understand something. I tried to translate at least the name of the steps