Forum Discussion

KrisF's avatar
KrisF
Frequent Visitor
3 years ago
Solved

list.dates generates DataFormat.Error: We couldn't convert to Date

Hello,  Within a Dataflow, I am attempting to generate a list of dates between a Start and and End Date however when i create the custom column the "DataFormat.Error: We couldn't convert to Date" er...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi KrisF ,

     

    I'd do it this way:

     

    Create a new column for a bounded end date that's not 7,000 years in the future:

    endDateFilled =
if Date.Year([End_Date]) > 3000 or [End_Date] = null then Date.From(DateTime.LocalNow())
else [End_Date]

     

    Create another new column for your date list:

    List.Transform(
    { Number.From([Start_Date])..Number.From([endDateFilled]) },
    each List.Distinct(Date.EndOfMonth(Date.From(_)))
)

     

    You should now be able to expand this new column for your list of month-end dates.

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
3 years ago

Hi KrisF ,

 

I'd do it this way:

 

Create a new column for a bounded end date that's not 7,000 years in the future:

endDateFilled =
if Date.Year([End_Date]) > 3000 or [End_Date] = null then Date.From(DateTime.LocalNow())
else [End_Date]

 

Create another new column for your date list:

List.Transform(
    { Number.From([Start_Date])..Number.From([endDateFilled]) },
    each List.Distinct(Date.EndOfMonth(Date.From(_)))
)

 

You should now be able to expand this new column for your list of month-end dates.

 

Pete

KrisF's avatar
KrisF
Frequent Visitor
3 years ago

Hi BA_Pete 

I had a chuckle at the 7000 years in the future, we can be ambitious some times 🙂

The suggestion you have provided works right up until i split or expand the list  where the following error is generated:

Expression.Error: We cannot convert the value #date(2022, 7, 31) to type List.

Edit: i note the data type after the expansion is "any" where i chose "Date" at point of column creation. 
Thanks again for your assistance to date.

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

     

    Go to a step in your query before you do these transformations. Go to the home tab > Keep Rows (dropdown) > Keep Top Rows, then enter 10 in the dialog.

    Go through the query steps and see if you still get the error.

    If you don't, add another 10/20 etc. rows etc. and keep going until you get the error again. You now have the range of rows in which your error is occurring.

    Check these rows to see if there's anything obviously different about them that you can see.

     

    If you can't see anything obvious yourself, filter your table to just the rows that are producing the error, then copy the whole table using the button at the top left of the grid in PQ, paste it into Home tab > Enter Data, then open Advanced Editor for this new, pasted table, copy all the code in there and paste it into a code window ( </> button ) here. I'll be able to have a look at what's going on myself then.

     

    Pete