cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Champion
Community Champion

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

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
Highlighted
Resolver I
Resolver I

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

@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

 

Highlighted
Helper II
Helper II

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

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

Highlighted
Community Champion
Community Champion

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

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

Highlighted
Helper II
Helper II

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

Hi @MarcelBeug thanks!!!

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors