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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors