Forum Discussion
Helper VCreate a date-time field from a date column and a number column with the time in "DirectQuery Mode"
I need help joining two columns, one containing values in date format and the other containing the time in number format in DirectQuery mode.
13 Replies
- MasonMA
Super User
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)
telesforo1969Thank 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-msftCommunity Support
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 numberDateTime = '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.
- 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.
- 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. - 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.
