Create a date-time field from a date column and a number column with the time in "DirectQuery Mode"

[telesforo1969]

  I need help joining two columns, one containing values ​​in date format and the other containing the time in number format in DirectQuery mode.

[V-yubandi-msft]

Hi @telesforo1969 ,

May I know if your issue has been resolved, or if you still need any additional details? Please let us know if you need any further assistance.
 

Thank you.

[V-yubandi-msft]

Hi @telesforo1969 ,

I wanted to check if you had the opportunity to review the information provided by @MasonMA , @Omid_Motamedise . Please feel free to contact us if you have any further questions. 
 

Thank you for your valuable input, @MasonMA , @Omid_Motamedise .

Regards,
Yugandhar.

[Omid_Motamedise]

Hi,

You can add a new column using the following formula

 [Fecha]& #time([Hora],0,0)

for detail see the attached file

[telesforo1969]

Thank you very much, but I am connected in DirectQuery mode and it does not allow the transformation; it asks me to switch to Import mode.

[telesforo1969]

Thank you very much, but I am connected in DirectQuery mode and it does not allow the transformation; it asks me to switch to Import mode.

[MasonMA]

Hi!

If this is a DirectQuery to SQL, you may leverage Power Query to create a new Custom Column for DateTime with this sample M code for the last added step, 

= Table.AddColumn(PreviousStep, "DateTime", each [Date] + #duration([Time],0,0,0), type datetime),

But sometimes, my reports are also using DirectQuerty to other Semantic Models as data source, in this case i would normally change the connection mode to Composite, then Power BI imports metadata from the semantic model but allows local calculated tables/columns.

With Composite model, you would be able to create a new column with DAX , 

DateTime =
'Table'[Date] + TIME('Table'[Time], 0, 0)

[telesforo1969]

Thank you very much for the support, but your proposal yields the following result: it changes the dates and always shows the time as 12:00:00

[V-yubandi-msft]

Hi @telesforo1969 ,

Thank you for testing, you're doing a good job exploring different solutions. You’re correct if the [Hora] column contains just a number, the DAX formula may default to 12:00:00, which is not the intended result.

To resolve this, we should adjust the formula to match the format of your [Hora] column. Here are a few options based on how your data is set up.
1. If [Hora] is a whole number

DateTime = 
'Table'[Fecha] + TIME(INT('Table'[Hora]), 0, 0)

2. If [Hora]  is already in time format

DateTime = 
'Table'[Fecha] + 'Table'[Hora]

 3.If [Hora] includes decimals

DateTime = 
'Table'[Fecha] + TIME(INT('Table'[Hora]), MOD('Table'[Hora]*60, 60), 0)

Try and Let me know how your [Hora] column is set up whether it's an integer, decimal, or actual time. You’re really close to getting this working smoothly.

Regards,
Yugandhar.