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.

[telesforo1969]

I added an additional step because I had to change my hour period (1-24) to (0-23), but the format sets it as shown in the images. When I change it to integer type, it doesn't allow me.

[V-yubandi-msft]

Hi @telesforo1969 ,

Thank you for your time and the update. In DirectQuery mode, changing data types or adding columns isn’t supported. It’s best to update your SQL source using DATEADD(HOUR, Hora - 1, Fecha) or switch to a Composite model and use DAX to create the DateTime column.

Hope this helps resolve the issue.

Regards,
Yugandhar.

[telesforo1969]

I understand. Thank you very much for the support, and I will consult with the database team about the possibility of adding the information.

[V-yubandi-msft]

Sure, @telesforo1969 . Once you connect with the database team , please let us know whether it’s possible or not. For further investigation, we’ll proceed based on the database team’s inputs.

Thanks for staying engaged with the community.

[V-yubandi-msft]

Hi @telesforo1969 ,

Have you had a chance to try the suggested solution? Please let us know if the issue is resolved or if you need any additional details.  We’re happy to help if you need further assistance.

Thank you.