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
yoav20007
Helper II
Helper II

how to create calculate column in Quary Editor " if " formula?

I need to add a column with if formula in query editor

if "Alldates.qtty" column is null and the weekday column is not Friday or satardy than the value is 0 . (att pic)

if statement don't work like excel in query editor? 

 

Thanks 

 

query editor.JPG

 

 

1 ACCEPTED SOLUTION

The issues are the parentheses around null ("null") and the precedence of or and and.

e.g. a or b or c or d and e is equivalent with a or b or c or (d and e).

 

Anyhow, try:

 

= Table.AddColumn(#"Renamed Columns5", "Custom", each if (not List.Contains({"Friday","Saturday"},[weekday])) and [All_Dates.qtty] = null then 0 else null)

 

Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
ccakjcrx
Resolver I
Resolver I

@yoav20007

 

Hey!

 

I don't have much experience with custom conditional columns and formulas in pbi's query editor. I searched for problems similar to yours, and found that the if statements in this area are significantly different than what is used in excel. 

 

The following link takes you to a solution in the pbi community I found very educational: problem/solution thread

 

After reviewing that info, and testing it seems you will need to add a custom column and use the following formula: 

 

if[weekday] = "Monday" or [weekday]="Tuesday" or [weekday]="Wednesday" or [weekday]="Thursday" or [weekday]="Sunday" and [all_dates.qtty] = "null" then "0" else "null"

 

I tried to research using 'not' in query editor when adding a custom column, but couldn't find anything in a reasonable amount of time.

 

I made the assumption that you wanted to leave the field to 'null' if all_dates.qtty is null and weekday is either Friday or Saturday.

 

Weekday If Then Else.jpg

 

Hi @CC and thanks for your time

I try to add this to my data and it doesn't work for Sunday.  i don't understand why. see pic.

do you have any idea ? 

 

query editor2.JPG

The issues are the parentheses around null ("null") and the precedence of or and and.

e.g. a or b or c or d and e is equivalent with a or b or c or (d and e).

 

Anyhow, try:

 

= Table.AddColumn(#"Renamed Columns5", "Custom", each if (not List.Contains({"Friday","Saturday"},[weekday])) and [All_Dates.qtty] = null then 0 else null)

 

Specializing in Power Query Formula Language (M)

Hi @MarcelBeug thanks!!!

 

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.