Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BNiddrieDavies
Regular Visitor

We cannot convert a value of type Function to type Logical IF function

Hello

I am trying to put a "List.AnyTrue" function inside an "if" function, but dont know the correct syntax. In the example below, i want to put "addedCustom1" inside "addedCustom"...which i have tried to do.... but this comes back with "We cannot convert a value of type Function to type Logical".

Any help greatly appreciated.

Thank you

 

Ben

 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Je Header Id", Int64.Type}, {"Description", type text}, {"Total", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Basic Pay1", each if (F1)=> List.AnyTrue(List.Transform(Basic,each Text.Contains(F1[Description], _))) = true then [Total] else 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Basic Pay 2", each if [Basic Pay1] = true then [Total] else 0)
in
#"Added Custom1"

1 ACCEPTED SOLUTION
artemus
Employee
Employee

change

each if (F1)=>

to

(F1) => if

View solution in original post

5 REPLIES 5
BNiddrieDavies
Regular Visitor

Just to confirm for other users: formula is now written like the below and works thanks to @artemus :

 

Formula is now written like:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Je Header Id", Int64.Type}, {"Description", type text}, {"Total", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Basic Pay1", (F1)=> if List.AnyTrue(List.Transform(Basic,each Text.Contains(F1[Description], _)))=true then F1[Total] else 0 )
in
#"Added Custom"

BNiddrieDavies
Regular Visitor

Hi Artemus,

Thank you for the answer, that has worked and I think the first part of the formula within List.AnyTrue is working because the results are now split into "0"'s..for the "Falses" and errors for the "Trues", the error for the "Trues" is:

"Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

Formula is now written like:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Je Header Id", Int64.Type}, {"Description", type text}, {"Total", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Basic Pay1", (F1)=> if List.AnyTrue(List.Transform(Basic,each Text.Contains(F1[Description], _)))=true then [Total] else 0 )
in
#"Added Custom"

Are you able to advise on the error?
Thank you

you would need to use F1[Total] instead of just [Total]

Yaaaaaas!!!
Thank you, that has done it!!!
Hero! Many thanks

artemus
Employee
Employee

change

each if (F1)=>

to

(F1) => if

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors