How do I create a DateTime field from a Date field and a Number field

[d474boy]

Hi everyone, this is my first ever post to the Power BI Community Forums

I am also very new to Power Query and the M Language.

Anyway, I have a query that is pulling out two fields which are 'Completed Date' and 'Completed Time' but the Time is just a decimal number. The Decimal number is something like 10.3 but this acually means 10:30.

I want to combine the Date and Number into a single column called Completed_DateTime. In Excel this would be as simple as the following formula:

Completed_DateTime = Completed_Date + INT(Number) / 24 + 100 * MOD(Number,1) / 24 / 60

The best I have come up with in Power Query is:

#"Add Temp End_DateTime col" = Table.AddColumn(#"Remove 1-1-1900 Dates", "Actual_End_DateTime", each [Actual_End_Date]),
#"Change Type of temp col to DateTime" = Table.TransformColumnTypes(#"Add Temp End_DateTime col",{{"Actual_End_DateTime", type datetime}}),
#"Create the real End_DateTime" = Table.AddColumn(#"Change Type of temp col to DateTime", "Actual_End_DateTime2", each [Actual_End_DateTime] + #duration(0,Number.RoundDown([Actual_End_Time]),Number.RoundDown(100*Number.Mod([Actual_End_Time],1)),0)),

Surely it could be done in a single step. For example, can I create

[v-juanli-msft]

Hi @d474boy 

Add a custom column

Text.Combine
({
   Text.From(Date.AddDays([Completed Date], Int64.From(
    [Completed Time]/24)), "en-US"), 
   Text.From(Text.Combine
       ({Text.From(Int64.From([Completed Time]), "en-US"),
         Text.From(Int64.From(100*Number.Mod([Completed Time],1)), "en-US")}, ":"),
    "en-US")}, 
":")

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gpoggi]

Hi @d474boy 

Yep, there are differente ways to accomplish this, for example if your Completed_Date is never null you can try just this line:

#datetime(Date.Year([Completed_Date]),Date.Month([Completed_Date]), Date.Day([Completed_Date]), Number.RoundDown([Number]),Number.Mod([Number],1)*100,0)

if you have nulls then add a simple condition:

if
  [Completed_Date] = null
then
  null
else 
  #datetime(Date.Year([Completed_Date]),Date.Month([Completed_Date]), Date.Day([Completed_Date]), Number.RoundDown([Number]),Number.Mod([Number],1)*100,0)

Hope this helps,

Any question, just let me know,

Regards, 

Gian Carlo Poggi

[d474boy]

Hi @gpoggi 

I have worked out how to add your clever bit of code in, as below:

#"Rename Raw Fieldnames" = Table.RenameColumns(#"Format DataDatetime as datetime",{{"ORDER_C", "OrderNumber"}, {"CONTRACT_C", "ContractCode"}, {"CRSTAMP_C", "ReceivedDatetime"}, {"DESC_C", "OrderDescription"}, {"PRIORITY_C", "PriorityCode"}, {"CMPRDT_C", "ActualEndDate"}, {"SCTIME_C", "ActualEndTime"}, {"RTREATM_C", "JobType"}, {"USRN_C", "USRN"}, {"LOCN_C", "DefectLocation"}}),
#"Remove 1-1-1900 Dates" = Table.ReplaceValue(#"Rename Raw Fieldnames",#date(1900, 1, 1),null,Replacer.ReplaceValue,{"ActualEndDate"}),
#"Create CompletedDatetime" = Table.AddColumn(#"Remove 1-1-1900 Dates", "CompletedDatetime", each if
[ActualEndDate] = null
then
null
else
#datetime(Date.Year([ActualEndDate]),Date.Month([ActualEndDate]), Date.Day([ActualEndDate]), Number.RoundDown([ActualEndTime]),Number.Mod([ActualEndTime],1)*100,0)),
#"Removed Columns" = Table.RemoveColumns(#"Create CompletedDatetime",{"ActualEndDate", "ActualEndTime"}),

However, this is giving an error when the number field (ActualEndTime) is not a whole number. The field is formatted as DecimalNumber and has values for sequential rows of: 0, 14, 10.42 etc.

I have overcome the error by adding a Number.Round() to the Number.Mod([ActualEndTime],1)*100 part, so my code atually reads:

#"Rename Raw Fieldnames" = Table.RenameColumns(#"Format DataDatetime as datetime",{{"ORDER_C", "OrderNumber"}, {"CONTRACT_C", "ContractCode"}, {"CRSTAMP_C", "ReceivedDatetime"}, {"DESC_C", "OrderDescription"}, {"PRIORITY_C", "PriorityCode"}, {"CMPRDT_C", "ActualEndDate"}, {"SCTIME_C", "ActualEndTime"}, {"RTREATM_C", "JobType"}, {"USRN_C", "USRN"}, {"LOCN_C", "DefectLocation"}}),
#"Remove 1-1-1900 Dates" = Table.ReplaceValue(#"Rename Raw Fieldnames",#date(1900, 1, 1),null,Replacer.ReplaceValue,{"ActualEndDate"}),
#"Create CompletedDatetime" = Table.AddColumn(#"Remove 1-1-1900 Dates", "CompletedDatetime", each if
[ActualEndDate] = null
then
null
else
#datetime(Date.Year([ActualEndDate]),Date.Month([ActualEndDate]), Date.Day([ActualEndDate]), Number.RoundDown([ActualEndTime]),Number.Round(Number.Mod([ActualEndTime],1)*100),0)),
#"Removed Columns" = Table.RemoveColumns(#"Create CompletedDatetime",{"ActualEndDate", "ActualEndTime"}),

I don't know why this adjustment was necessary but at least it works.

Thanks for your help.

[gpoggi]

Hi @d474boy,

It's probably because of the input data, if you can get it fixed with Number.Round before the Number.Mod, great, if not just send an screen of your table to see how is the data was generated until step #"Remove 1-1-1900 Dates". And I would help you to find the reason about why is that error being showed.

Any question, just let me know.

Regards,

Gian Carlo Poggi

[d474boy]

Hi @gpoggi 

The full code is:

let
Source = Sql.Databases("XXXXXXXXXXXXX"),
cpa_LIVE = Source{[Name="YYYYYYYYY"]}[Data],
dbo_SWOL = cpa_LIVE{[Schema="dbo",Item="SWOL"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_SWOL, each [SN] = "506501" and ([CONTRACT_C] = "GH1901" or [CONTRACT_C] = "GH2001")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"CONTRACT_C", "ORDER_C", "CMPRDT_C", "CRSTAMP_C", "LOCN_C", "PRIORITY_C", "SCTIME_C", "RTREATM_C", "USRN_C", "DESC_C"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"CMPRDT_C", type date}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"LOCN_C", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"LOCN_C", Text.Clean, type text}}),
#"Trimmed Text1" = Table.TransformColumns(#"Cleaned Text",{{"DESC_C", Text.Trim, type text}}),
#"Cleaned Text1" = Table.TransformColumns(#"Trimmed Text1",{{"DESC_C", Text.Clean, type text}}),
#"Create TownName field" = Table.AddColumn(#"Cleaned Text1", "TownName", each Text.Start([LOCN_C],Text.PositionOf([LOCN_C]," ")+1)),
#"Reorder Columns" = Table.ReorderColumns(#"Create TownName field",{"ORDER_C", "CRSTAMP_C", "DESC_C", "PRIORITY_C", "CMPRDT_C", "RTREATM_C", "USRN_C", "LOCN_C", "TownName"}),
#"Add DataDatetime field" = Table.AddColumn(#"Reorder Columns", "DataDatetime", each DateTime.LocalNow()),
#"Format DataDatetime as datetime" = Table.TransformColumnTypes(#"Add DataDatetime field",{{"DataDatetime", type datetime}}),
#"Rename Raw Fieldnames" = Table.RenameColumns(#"Format DataDatetime as datetime",{{"ORDER_C", "OrderNumber"}, {"CONTRACT_C", "ContractCode"}, {"CRSTAMP_C", "ReceivedDatetime"}, {"DESC_C", "OrderDescription"}, {"PRIORITY_C", "PriorityCode"}, {"CMPRDT_C", "ActualEndDate"}, {"SCTIME_C", "ActualEndTime"}, {"RTREATM_C", "JobType"}, {"USRN_C", "USRN"}, {"LOCN_C", "DefectLocation"}}),
#"Remove 1-1-1900 Dates" = Table.ReplaceValue(#"Rename Raw Fieldnames",#date(1900, 1, 1),null,Replacer.ReplaceValue,{"ActualEndDate"}),
#"Create CompletedDatetime" = Table.AddColumn(#"Remove 1-1-1900 Dates", "CompletedDatetime", each if
[ActualEndDate] = null
then
null
else
#datetime(Date.Year([ActualEndDate]),Date.Month([ActualEndDate]), Date.Day([ActualEndDate]), Number.RoundDown([ActualEndTime]),Number.Round(Number.Mod([ActualEndTime],1)*100),0)),
#"Removed Columns" = Table.RemoveColumns(#"Create CompletedDatetime",{"ActualEndDate", "ActualEndTime"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"OrderNumber", "ContractCode", "ReceivedDatetime", "CompletedDatetime", "OrderDescription", "PriorityCode", "JobType", "USRN", "DefectLocation", "TownName", "DataDatetime"})
in
#"Reordered Columns"

I have noticed that the field type for the SCTIME_C" (renamed to "ActualEndTime") field is Decimal Number. What I suspect is that the #datetime function requires that all its arguments are integer. So, even though all the values in the SCTIME_C field only ever have 2 decimal places (e.g. 10.59) and hence when doing the * 100 the results will always be integer (e.g. 59) the #datetime cannot be certain of this and so rejects the values. However, when the value in the SCTIME_C field was just something like 13 then the #datetime function worked fine, i.e. it would create a datetime of 04/04/2019 13:00:00 for example. So, why this inconsistency?

[d474boy]

Hi @gpoggi 

Thanks for the help.

How do I actually implement that code? I don't understand the syntax well enough to just slip this in. I have copied in the section of my code from a bit before the bit that creates the field to just after it. Please could you add in your bit (shown below) exacly in the required syntax.

if
  [Completed_Date] = null
then
  null
else 
  #datetime(Date.Year([Completed_Date]),Date.Month([Completed_Date]), Date.Day([Completed_Date]), Number.RoundDown([Number]),Number.Mod([Number],1)*100,0)

#"Remove 1-1-1900 Dates" = Table.ReplaceValue(#"Rename Raw Fieldnames",#date(1900, 1, 1),null,Replacer.ReplaceValue,{"ActualEndDate"}),
#"Add TempCompletedDatetime" = Table.AddColumn(#"Remove 1-1-1900 Dates", "TempCompletedDatetime", each [ActualEndDate]),
#"Change Type of Temporary field to DateTime" = Table.TransformColumnTypes(#"Add TempCompletedDatetime",{{"TempCompletedDatetime", type datetime}}),
#"Create the real CompletedDateTime" = Table.AddColumn(#"Change Type of Temporary field to DateTime", "TempCompletedDatetime2", each [TempCompletedDatetime] + #duration(0,Number.RoundDown([ActualEndTime]),Number.RoundDown(100*Number.Mod([ActualEndTime],1)),0)),
#"Removed Columns" = Table.RemoveColumns(#"Create the real CompletedDateTime",{"TempCompletedDatetime", "ActualEndDate", "ActualEndTime"}),

For example, to show how little I understand, I tried the following using the simpler version of your solution:

#"Remove 1-1-1900 Dates" = Table.ReplaceValue(#"Rename Raw Fieldnames",#date(1900, 1, 1),null,Replacer.ReplaceValue,{"ActualEndDate"}),
#"Add TempCompletedDatetime" = Table.AddColumn(#"Remove 1-1-1900 Dates", "TempCompletedDatetime", each #datetime(Date.Year([Completed_Date]),Date.Month([Completed_Date]), Date.Day([Completed_Date]), Number.RoundDown([Number]),Number.Mod([Number],1)*100,0),
#"Change Type of Temporary field to DateTime" = Table.TransformColumnTypes(#"Add TempCompletedDatetime",{{"TempCompletedDatetime", type datetime}}),
#"Create the real CompletedDateTime" = Table.AddColumn(#"Change Type of Temporary field to DateTime", "TempCompletedDatetime2", each [TempCompletedDatetime] + #duration(0,Number.RoundDown([ActualEndTime]),Number.RoundDown(100*Number.Mod([ActualEndTime],1)),0)),
#"Removed Columns" = Table.RemoveColumns(#"Create the real CompletedDateTime",{"TempCompletedDatetime", "ActualEndDate", "ActualEndTime"}),

Many thanks

Gareth