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 

 

Im calling this solved, however i went about it slightly differently. This is the process i used after your suggestion to fix the End_Date field:

  1. Added the New End_Date field per your suggestion
  2. Added the DayDiff field using the PowerQuery process
  3. Added a list of all days using the following M:
    Table.AddColumn(#"Calc Day Diff End - Start", "AllDates", each List.Dates([Start_Dttm],[Subtraction]+1,#duration(1,0,0,0)))
  4. Expanded List successfully
  5. Merged with a aDate table that i created that has the EOM field aligned to the date. This allowed for equal cardinality, however created a mountain of duplications.
  6. Removed all date columns and Subtraction column 
  7. Removed all duplicates in table
  8. End. result is a distinct list of rows aligned to each month the customer was present and cross check accuratly with other sources.

Thanks for your suggestion. I will look to figure out where the issue is with the List Date suggestion you had a little later.