cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DaleORL
Frequent Visitor

DATAVERSE DATAFLOW Fails to import into datetime

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_0-1658927438114.png

1 ACCEPTED SOLUTION

Accepted Solutions
DaleORL
Frequent Visitor

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

View solution in original post

9 REPLIES 9
DaleORL
Frequent Visitor

  #"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(000)}, {"EventStartHr", #time(000)}}),
  #"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(000), 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(1971101000), 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
Frequent Visitor

This is the powerquery preview of datetime as text at the last step
DaleORL_0-1658927881578.png

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
Super User
Super User

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

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

 

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

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

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

 

AhmedSalih_0-1659017704256.png

 

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_0-1659020712795.png

 

DaleORL
Frequent Visitor

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (4,951)