Forum Discussion

smjzahid's avatar
smjzahid
Icon for Helper V rankHelper V
4 years ago

Data Format.Error We couldn't parse the input provided as date value

I have power BI dataflow and I am connecting to this via import connection. and then this goes through few steps (see snip below) When I close and apply it then pulls all data from PBI dataflow and ...
Need Help
Show and Tell
smjzahid's avatar
smjzahid
Icon for Helper V rankHelper V
4 years ago

Hi AlexisOlson  Thanks for your reply, tried your suggestion, still getting the error, even tried to split this date column with / and into Day/Month/Year and later merged it converted to date no erros until the final step, as soon as I hit close and apply in query editor, it then loads all data (approx 39 MB) from PBI dataflow and throw this error, (Data Frrmat Error)

 

 

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

Is the error coming from one of the tables you're merging in (e.g. dimDates)? You might need to try a similar correction on those queries.

  • smjzahid's avatar
    smjzahid
    Icon for Helper V rankHelper V
    4 years ago

    Thanks AlexisOlson Yes I have tried similar transformation on all tables being used in Merge for eg: (factRiskRegister, Dimdates) see below snip for the transformation applied and still the error pops up on Closed Date column, 

     

    Clicking on the error in power query inidicates there values shown in snip2

     

     

     

    tried everything I could like split columns by delimiter, and then later merged them back to remove the time component from the column, but then when I close and apply the refresh fails

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      OK. Try using manual transformations that specify the format exactly.

       

      For example, instead of the Changed Type with Locale step, try this instead:

      = Table.TransformColumns(
    #"Previous Step Name",
    {{"DateCol", each DateTime.FromText(_, [Format = "dd/MM/yyyy HH:mm:ss"]), type datetime}}
)

       

       

      • smjzahid's avatar
        smjzahid
        Icon for Helper V rankHelper V
        4 years ago

        Thanks AlexisOlson  for your help, tried above code, instead of change locale type, the code works, fine, then I went down on the applied step till the very last step, no erros shown on Closed date column, then I CLOSE AND APPLY and then it load data and threw this error again