Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
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.
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 ?
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |