Forum Discussion
Creating Date/Time calendar
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
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
Hi v-danhe-msft
Thanks for your detailed comment.
I Followed your instructions, but i get an error at the end. ( "increment" is out of limit )
My date/Time columns, don't contain seconds. Maybe that's the problem ?Hi Ghaston,
I have reproduecd your problem. You could check the code in the advanced language, when you are using the Duration.from function, the parameter should be a bigger one to minus a smaller one, see the picture post below, if you used a smaller one to minus the bigger one, it will show the same error, I suggest to check the value and modify the code.
Regards,
Daniel He
Hi v-danhe-msft
you were right! i had to modify the code like you showed me.
I don't get that error now, but instead, i get a calendar that starts from my bigger column (date/time in column A) , to 2021 !
I verified my A and B tables, and they are fine.
