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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Rjesak
Helper I
Helper I

Calculated column based on two other columns

I've found a number of posts that seem like they should help but I haven't been able to get this to work.

 

I am in the Edit Query section and I select add Custom Column. I want to set the column based on two other column values - COLUMN1 contains 'A' and COLUMN2 contains 'B' then the custom column = 1.

 

This formula:

if Text.Contains([COLUMN1], "A") & Text.Contains([COLUMN2], "B") then 1 else 0

passes the syntax check but each result is an Error:

Expression.Error: We cannot apply operator type & to types logical and logical.

 

I've found some solutions on this board that suggest this should work:

if (Text.Contains([COLUMN1], "A") && Text.Contains([COLUMN2], "B"), 1, 0)

but that doesn't even pass the syntax check (Token literal expected).

 

This shouldn't be this hard so I suspect I'm missing something very simple but I'm completely stuck.

1 ACCEPTED SOLUTION
QuangMC
Frequent Visitor

Hey, i'm new to the forum, so forgive if the answer is wrong 😅

I've had this error as well when trying to add a condition. If memory serves, replace & by "and"

Like the following :

if Text.Contains([COLUMN1], "A") and Text.Contains([COLUMN2], "B") then 1 else 0

 

Edit : Just to further explain, the "&" in M language serves as a concatenate function, and not like an "AND" function. Since Power Query uses M language, and PBI uses DAX, you might mix them up. 

View solution in original post

2 REPLIES 2
QuangMC
Frequent Visitor

Hey, i'm new to the forum, so forgive if the answer is wrong 😅

I've had this error as well when trying to add a condition. If memory serves, replace & by "and"

Like the following :

if Text.Contains([COLUMN1], "A") and Text.Contains([COLUMN2], "B") then 1 else 0

 

Edit : Just to further explain, the "&" in M language serves as a concatenate function, and not like an "AND" function. Since Power Query uses M language, and PBI uses DAX, you might mix them up. 

Thank you SO much! That fixed it.

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors