Expression.Error: We cannot convert the value #date(2023, 1, 2) to type Text

[beatrizalbuqu]

Hello!

I've been trying to fix the error below but I can't figure why is this function is trying to convert a date to a text.

Expression.Error: We cannot convert the value #date(2023, 1, 2) to type Text.
Details:
Value=1/2/2023
Type=[Type]

= let
startdate = #date(2022,11,01),
enddate = #date(2022,11,30),

inicial = each if [FY START] <= startdate then startdate as date else [FY START] as date,
final = each if [FY END DATE] <= enddate  then [FY END DATE] as date else enddate as date,

in Table.AddColumn(#"November days", "November holidays", List.Count(List.Select(Table.ToList(#"UK Holidays"),each [#"UK Holidays.Date"] >= inicial and [#"UK Holidays.Date"] <= final)), type number)

Just a quick explanation about the sources and what I need as output:

#"November days" is the table where I have a count of days between two dates in the month of November/22.
inicial and final dates are functions that define the dates I used on the count function mentioned above:
inicial is the the start working date of an employee on November and final is the final working date of an employee on November.

#"UK Holidays" is the table with all the holidays on the UK and I want to refer to it in order to count the number of holidays there are contained in my range of dates (final - inicial). It has a column called "Date" which type is date.
I've identified that the error message is referring to the first item on the "Date" column of #"UK Holidays": #date(2023, 1, 2)
PS: #date(2023, 1, 2) the first holiday typed on this table and I know it's not on November/22 so it shouldn't be counted in my output, but there are other dates that should be counted.

All the dates I'm using below are set as type date but I've tried using Date.From as an attempt of fixing the error but it didn't work (it results on the same error):

List.Select(Table.ToList(#"UK Holidays"),each [#"UK Holidays.Date"] >= inicial and [#"UK Holidays.Date"] <= final

Does anyone knows how to fix this problem?

Thanks!
Beatriz

[tsibilski]

In one of the steps in the query I had a function causing that issue when in the column where Power Query expected to find text, for example, C or A, it was finding a date. The solution was to fix the source file (Excel) removing dates from the column where should be text or put Text.From() around the column name within Text.Contains() function. A piece of code causing the issue in my case: 
each Text.Contains([#"Bank Details (C/A/O)"], "C") or ....

[m_dekorte]

Hi @beatrizalbuqu,

See my earlier reply in your related post here.

There's a clear issue with the code above, you're missing an each- or function-expression in the 3rd argument for the "columnGenerator" where you've entered: List.Count( ...

Table.AddColumn(#"November days", "November holidays", List.Count(List.Select(Table.ToList(#"UK Holidays"),each [#"UK Holidays.Date"] >= inicial and [#"UK Holidays.Date"] <= final)), type number)

and you appear to be having an environment issue in this expression as well a simplified explanation can be found here.

I hope this will help to resolve it.

Cheers

[beatrizalbuqu]

Hi m_dekonorte,

Thanks again for your help!

I've inserted an each before "List.Count( " and it did resolved one of the problems but I still have the same error as a result: "Expression.Error: We cannot convert the value #date(2023, 1, 2) to type Text"

I don't understand why is it trying to convert a date to text on the second argument of a "List.Select(" where all the terms in the expression are dates.

Here's the new code I've used within what I've learned from your comments:

= let
startdate = #date(2022,11,01),
enddate = #date(2022,11,30),

result = Table.AddColumn(#"November days", "November holidays", each List.Count(List.Select(Table.ToList(#"UK Holidays"),each Table.Column(#"UK Holidays","Date") >= (if [FY START] <= startdate then startdate as date else [FY START]) and Table.Column(#"UK Holidays","Date") <= (if [FY END DATE] <= enddate  then [FY END DATE] as date else enddate))), type number)

in result

[m_dekorte · Accepted solution]

Looking again there appears to be an additional issue
is this [#"UK Holidays.Date"] meant to refer to itemss in this list: Table.ToList(#"UK Holidays") ?

If so try this instead:
List.Count( List.Select( Table.ToList(#"UK Holidays"), (x)=> x >= inicial and x <= final ))

if you're still getting a type conversion error, break your formula down and return each component to validate that it is returning the correct value and type you require. I find that nesting a record is a convenient way to do just that, that will look something like this.

Table.AddColumn( #"November days", "November holidays", each
    [
        h = Table.ToList(#"UK Holidays"),
        i = inicial,
        f = final,
        a = List.Select( h, (x)=> x >= i and x <= f ),
        b = List.Count( a )
    ]
)

When the record is returned, click off to the side in the whitespace to see its content, in an additional preview window below the main one.

I hope this helps you to resolve it.

[beatrizalbuqu]

Thank you,

It worked!