Forum Discussion

d474boy's avatar
d474boy
Icon for Helper I rankHelper I
7 years ago

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

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 ...
Need Help
d474boy's avatar
d474boy
Icon for Helper I rankHelper I
7 years ago

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's avatar
gpoggi
Icon for Responsive Resident rankResponsive Resident
7 years ago

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's avatar
    d474boy
    Icon for Helper I rankHelper I
    7 years ago

    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?