DATAVERSE DATAFLOW Fails to import into datetime

[DaleORL]

i am importing from SharePoint List to DATAVERSE. within the advanced editor I am creating a new column from a Date column and conversion of a text column to time into a new datetime column then converting to text.

I can preview the rows but anytime the dataflow runs it fails. i have attempted all sorts of different datetime approaches. the last reference i found said to load as text separated date and time with a space.

here are the relevant advanced editor rows.

[DaleORL]

  #"Hour To Time" = Table.TransformColumns(#"Custom 4", {{"EventStartHr", each Time.FromText(_), type time}, {"EventEndHr", each Time.FromText(_), type time}}),

  #"Replaced value 16" = Table.ReplaceValue(#"Hour To Time", "", "_tbd", Replacer.ReplaceValue, {"EventType"}),

  #"Replaced value 17" = Table.ReplaceValue(#"Replaced value 16", "", "_TBD", Replacer.ReplaceValue, {"Package"}),

  #"Added custom" = Table.TransformColumnTypes(Table.AddColumn(#"Replaced value 17", "DFAPID2", each [DFAPID] + 54321), {{"DFAPID2", Int64.Type}}),

  #"Replaced errors" = Table.ReplaceErrorValues(#"Added custom", {{"EventEndHr", #time(0, 0, 0)}, {"EventStartHr", #time(0, 0, 0)}}),

  #"Changed column type 7" = Table.TransformColumnTypes(#"Replaced errors", {{"EventStart", type date}, {"EventEnd", type date}}),

  #"Replaced value 18" = Table.ReplaceValue(#"Changed column type 7", null, #time(0, 0, 0), Replacer.ReplaceValue, {"EventEndHr", "EventStartHr"}),

  #"Inserted merged column" = Table.TransformColumnTypes(Table.AddColumn(#"Replaced value 18", "Starting", each Text.Combine({Text.From([EventStart]) & " " & Text.From([EventStartHr])})), {{"Starting", type text}}),

  #"Inserted merged column 1" = Table.TransformColumnTypes(Table.AddColumn(#"Inserted merged column", "Ending", each Text.Combine({Text.From([EventEnd]) & " " & Text.From([EventEndHr])})), {{"Ending", type text}}),

  #"replacenull start" = Table.ReplaceValue(#"Inserted merged column 1", null, #datetime(1971, 10, 1, 0, 0, 0), Replacer.ReplaceValue,{"Starting", "Ending"}),

  #"Replaced errors 1" = Table.ReplaceErrorValues(#"replacenull start", {{"Starting", "10/01/1971"}}),

  #"Replaced errors 2" = Table.ReplaceErrorValues(#"Replaced errors 1", {{"Ending", "10/01/1971"}})

[DaleORL]

This is the powerquery preview of datetime as text at the last step

when the dataflow runs all rows give this type of error
{"error":{"code":"0x0","message":"An error occurred while validating input parameters: Microsoft.OData.ODataException: Cannot convert the literal '1:00 PM' to the expected type 'Edm.Int32'. ---> System.FormatException: Input string was not in a correct format.

[AhmedSalih]

Hello, @DaleORL, are you creating a new Dataverse table or loading the data into an existing table?

Regards,

Ahmed

If my reply helped you, please give a 👍. And if it has solved your issue, please consider a 👍 & Accepting it as the Solution to help other members of the community find it more.

My Blog: www.powerplatformplace.com

[DaleORL]

@AhmedSalih . This is a custom dataverse table i created. the column type is datetime.

[AhmedSalih]

@DaleORL, I think the issue is you might not be able to load text formatted data into Date and Time column. Would you be able to reformat your columns to date time in Power Query?

[DaleORL]

@AhmedSalih i original tried loading as datetime and  that did not work. have tried all sorts of different methods. dates will load, time will not, nor datetime converted to text with a space between date and time.

[AhmedSalih]

@DaleORL, when your columns had the DataTime format in your Power Query, did you also have your DataTime Column setting in Dataverse Table as following:

Regards,

Ahmed

If my reply helped you, please give a 👍. And if it has solved your issue, please consider a 👍 & Accepting it as the Solution to help other members of the community find it more.

My Blog: www.powerplatformplace.com

[DaleORL]

[DaleORL · Accepted solution]

@AhmedSalih   disregard.... in my mapping i was taking the eventstarthr time column and trying to place it into a number. i didnt need this and chose none in mapping and everything came over.