Forum Discussion

Ghaston's avatar
Ghaston
Helper I
8 years ago
Solved

Creating Date/Time calendar

Hi guys !    I have a date/time column " Date_Heure" dans la table "SGD_CPT_LIGNE_LIVE_VUE" and another one that has the same name in "SGD_CPT_LIGNE_HISTO_VUE".  I want to create a date/time table...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    8 years ago

    Hi Ghaston,

    Please check the code in the advanced language like the picture below:

    The duration should from a smaller value, so you need modify the code from the B{0}[Column1].

     

    Regards,

    Daniel He

v-danhe-msft's avatar
v-danhe-msft
Microsoft Employee
8 years ago

Hi Ghaston,

Based on my test, you could refer to below steps:

Sample data:

Find the Earliest value in table A, the Latest value in Table B and convert to table.

Create a new blank query and enter the code in the advanced language:

let
        Source =List.DateTimes( A{0}[Column1],DayCount,#duration (0,0,1,0)),
        DayCount = Duration.TotalMinutes(Duration.From (B{0}[Column1] - A{0}[Column1]))+1,TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type datetime}})    

 in
        ChangedType

 

Now you could get the correct result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/3429u4vy08vvlp7/Time%20duration.pbix?dl=0

 

Regards,

Daniel He