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

[KrisF]

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" error is generated. 

The example data i am using is in this table:

Start_Date	End_Date	DayDiff	ID
6/12/2021	4/29/2022	321	1
4/30/2022	7/30/2022	91	1
7/31/2022	12/31/9999	50	1

The ultimate result i am looking to achieve is a list of dates that are the end of month dates the customer was present, The problem i am having is with an intermediate step. An example of the ultimate outcome:

End of Month	ID
31/07/2022	1
30/06/2022	1
31/05/2022	1
etc	1
etc	1
etc	1

The steps i have taken so fare include:

1. Changed Column Type of Start and End date columns to Date
2. Inserted date subtraction column using an IF statement that generates the value 50 for rows that have "12/31/9999". The date "12/31/9999" is used to indicate that the customer ID is still active.
3. Added a Custom column "AllDates" using the following M:
   Table.TransformColumnTypes(Table.AddColumn(#"Inserted date subtraction", "AllDates", each List.Dates([Start_Date], [DayDiff], #duration(1, 0, 0, 0))), {{"AllDates", type date}})
4. The error generated, sample:
   DataFormat.Error: We couldn't convert to Date.Details{#date(2021, 6, 12), #date(2021, 6, 13)..........

The result of this will then allow me to continue the process to return all end of month dates.

I am hoping to create the "List" and then expant that list to identify specific dates. 

Thanks in advance for your assistance.

[BA_Pete · Accepted solution]

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]

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. 

[KrisF]

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]

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