Hello, I am trying to write an IF statement to capture the following dates. I have and excel spreadsheet withs millions of records. For one of the columns in my spreadsheet, I want to create a new column to capture all DT_SCHED (date field) prior to June 1, 2017 and return a "Delivery Date of June 1st" and if the DT_SCHED date is greater than June 1st but less than December 31st, I want my IF statement to return "December 1st" delivery date. Could anyone help me figure this out? I don't have the Power Query Add-In to do a conditional lookup but have attached a screenshot with my first attempt but apparently the formula is VERY WRONG :)

Try this code: = if [DT_SCHED] < #date(2017,6,1) then "DELIVERY DATE JUNE 1ST" else if [DT_SCHED] < #date(2017,12,31) then "DELIVERY DATE DECEMBER 1ST" else null PQ is case sensitive and if...then...else is all lower case, just like null. Also mind your boundaries: in your information, June 1st is undefined; in the code above it returns December 1.Likewise, December 31, 2017 will return null, according to the specifications provided. How can you have millions of rows in Excel? As far as I know, Excel has a maximum of 1,048,576 rows

IF Statement using Power Query | Microsoft Fabric Community

21 Replies

lucilamorgan
New Member
10 days ago
MarcelBeug When utilizing conditional logic in Power Query, the IF statement plays a crucial role in transforming and categorizing data according to defined criteria. In your scenario, where it is necessary to generate a new column that designates a 'Delivery Date of June 1st' for all DT_SCHED values prior to June 1, 2017, and a 'Delivery Date of December 1st' for those falling between June 1 and December 31, you can employ the Add Column feature in Power Query and input a custom formula such as: = if [DT_SCHED] < #date(2017,6,1) then #date(2017,6,1) else if [DT_SCHED] <= #date(2017,12,31) then #date(2017,12,1) else null. This formula evaluates each date value and assigns the appropriate delivery date accordingly. Power Query's M language efficiently manages these conditions, even with extensive datasets, ensuring that your data remains consistent and automated without requiring manual filtering or Excel formulas.
lucilamorgan
New Member
10 days ago
When working with conditional logic in Power Query, the IF statement is essential for transforming and categorizing data based on specific criteria. In your case, where you need to create a new column that assigns a “Delivery Date of June 1st” for all DT_SCHED values before June 1, 2017, and a “Delivery Date of December 1st” for those between June 1 and December 31, you can use the Add Column feature in Power Query and enter a custom formula such as: = if [DT_SCHED] < #date(2017,6,1) then #date(2017,6,1) else if [DT_SCHED] <= #date(2017,12,31) then #date(2017,12,1) else null. This formula checks each date value and assigns the correct delivery date accordingly. Power Query’s M language handles these conditions efficiently, even with millions of records, ensuring that your dataset remains consistent and automated without the need for manual filtering or Excel formulas. playfy
When utilizing conditional logic in Power Query, the IF statement plays a crucial role in transforming and categorizing data according to defined criteria. In your scenario, where it is necessary to generate a new column that designates a 'Delivery Date of June 1st' for all DT_SCHED values prior to June 1, 2017, and a 'Delivery Date of December 1st' for those falling between June 1 and December 31, you can employ the Add Column feature in Power Query. You would input a custom formula such as: = if [DT_SCHED] < #date(2017,6,1) then #date(2017,6,1) else if [DT_SCHED] <= #date(2017,12,31) then #date(2017,12,1) else null. This formula evaluates each date value and assigns the appropriate delivery date accordingly. The M language of Power Query efficiently manages these conditions, even when dealing with millions of records, thereby ensuring that your dataset remains consistent and automated without requiring manual filtering or Excel formulas.
melissacroola
New Member
9 months ago
When working with conditional logic in Power Query, the IF statement is essential for transforming and categorizing data based on specific criteria. In your case, where you need to create a new column that assigns a “Delivery Date of June 1st” for all DT_SCHED values before June 1, 2017, and a “Delivery Date of December 1st” for those between June 1 and December 31, you can use the Add Column feature in Power Query and enter a custom formula such as: = if [DT_SCHED] < #date(2017,6,1) then #date(2017,6,1) else if [DT_SCHED] <= #date(2017,12,31) then #date(2017,12,1) else null. This formula checks each date value and assigns the correct delivery date accordingly. Power Query’s M language handles these conditions efficiently, even with millions of records, ensuring that your dataset remains consistent and automated without the need for manual filtering or Excel formulas. desi cinema
BasemSobhi
New Member
2 years ago
I found this video , it is more easier than writing a code: https://www.youtube.com/watch?v=HQ4RNJiVurI&t=4s
Anonymous
Not applicable
2 years ago
Hi Guys, I need to write this in Power query (powerbi) -> can someone help?

=IF(LEN(sellbydate)>1,IF(sellbydate<=(2024,10,31),"save","del"),"del")
- niiannang1
  New Member
  2 years ago
  Hi,
  Please I need help with a formula if I have a cheque date and a current date what formula will I use for the following conditions:
  Less than 1month -Green
  More than 1month but less than 3months-Amber
  More than 3 months at the cash office -Red
Tavo015
Regular Visitor
7 years ago
Why this code not run? Is it good?

if ([DÍAS DE VACACIONES PENDIENTES]) > 1 then "1" else Null

Thanks a lot
Tavo015
Regular Visitor
7 years ago
Why this code not run?

= Table.AddColumn(#"Added Custom", "PENDIENTES DE GOCE", each if ([DÍAS DE VACACIONES PENDIENTES]) > 1 then "1" else Null)

Thanks a lot
MarcelBeug
Community Champion
9 years ago
Try this code:

= if [DT_SCHED] < #date(2017,6,1) then "DELIVERY DATE JUNE 1ST" else if [DT_SCHED] < #date(2017,12,31) then "DELIVERY DATE DECEMBER 1ST" else null

PQ is case sensitive and if...then...else is all lower case, just like null.

Also mind your boundaries: in your information, June 1st is undefined; in the code above it returns December 1.
Likewise, December 31, 2017 will return null, according to the specifications provided.

How can you have millions of rows in Excel? As far as I know, Excel has a maximum of 1,048,576 rows
- Mo_15797
  Frequent Visitor
  2 years ago
  Hello, how can I use the IF function to return the value of column X if date in Column X is greater than Column Y, and if it's not greater for it to return the value of Column Y.
  
  Thanks,
- DMPBR
  Frequent Visitor
  6 years ago
  I signed up purely to give you kudos for this bit:
  MarcelBeug wrote:
  PQ is case sensitive and if...then...else is all lower case, just like null.
  
  My logic was right but turns out I had capitalised the THEN statement which threw an unexpected tag error.
- mdrammeh
  Helper III
  9 years ago
  I miss spoke Marcel. It's the database that has millions of queries and that's where am going in to filter by date range. You filter worked perfectly. THANK YOU, YOU GUYS ARE AWESOME!!
Sean
Community Champion
9 years ago
In the Query Editor try the Add Conditional Column feature

Add Column tab => Conditional Column button (then the code will be generated for you)
- marcoselias
  Advocate V
  8 years ago
  Sean,
  
  Thanks for pointing this out. It seems like it's going to help me but how can I do an AND or an OR to combine conditions? It looks like it only accepts one rule at a time. I'm trying to do something like:
  IF <condition1> AND <condition2> THEN <result> ELSE <alternate result>
  
  I hope I don't need to resort to creating multiple conditional columns.
  
  Thanks,
  Marcos
  - Nischal
    Helper I
    8 years ago
    hi , when i am trying to get two conditions using and function its throwing an error
- dkay84_PowerBI
  Microsoft Employee
  9 years ago
  The syntax for date values in Power Query is #date(year,month,day).
  
  Thus, your if then statement would say if [Date] <= #date(2017,6,1) then ...
  - Phil_Seamark
    Microsoft Employee
    9 years ago
    Agreed, the Syntax of your IF statement is good.
    
    Here is a nice breakdown on the subtle differences betwen the Excel IF and the Power BI IF
    
    http://www.excelguru.ca/blog/2014/08/27/the-if-function-in-power-query/