Forum Discussion

telesforo1969's avatar
telesforo1969
Icon for Helper V rankHelper V
10 months ago

Create 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.  
Need Help
MasonMA's avatar
MasonMA
Icon for Super User rankSuper User
10 months ago

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's avatar
telesforo1969
Icon for Helper V rankHelper V
10 months ago

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's avatar
    V-yubandi-msft
    Icon for Community Support rankCommunity Support
    10 months ago

    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.

    • V-yubandi-msft's avatar
      V-yubandi-msft
      Icon for Community Support rankCommunity Support
      10 months ago

      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.

    • telesforo1969's avatar
      telesforo1969
      Icon for Helper V rankHelper V
      10 months ago

      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's avatar
        V-yubandi-msft
        Icon for Community Support rankCommunity Support
        10 months ago

        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.