Forum Discussion
Expression.Error: We cannot convert the value #date(2023, 1, 2) to type Text
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.
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
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.
Thank you,
It worked!