Unanswered

DATAVERSE DATAFLOW Fails to import into datetime

(0) Share

Report

Posted on by 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.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (9)

Answers (1)

DaleORL 27 on at

Like (0)

Report

  #"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"}})

Was this reply helpful? Yes No
DaleORL 27 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
AhmedSalih 6,678 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
DaleORL 27 on at

Like (0)

Report

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

Was this reply helpful? Yes No
AhmedSalih 6,678 Moderator on at

Like (0)

Report

@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?

Was this reply helpful? Yes No
DaleORL 27 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
AhmedSalih 6,678 Moderator on at

Like (0)

Report

@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

Was this reply helpful? Yes No
DaleORL 27 on at

Like (0)

Report

Was this reply helpful? Yes No
Verified answer

DaleORL 27 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No