Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Help with IF multiple if statements

I have 4 columns with different values. I need a function that:   IF Column Value1 is empty, take Column Value2, and if this one is empty, take Column Value3, and if that one is empty, take Column ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Try this calculated column formula

    =if(isblank(Data[Column1]),if(isblank(Data[Column2]),if(isblank(Data[Column3]),Data[Column4],Data[Column3]),Data[Column2]),Data[Column1])

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Ashish_Mathur , I have the same problem but the formula is not working

 

I want to create a calculated column where

  • If Column 1 is blank get the date from
  • Column 2 but if this is blank get the dates from
  • Column 3 but if this is blank get the dates from
  • Column 4

    This is my data and the final dates is the Calculated column i want as outcome

 

Offer AcceptedOffer Accepted (On Boarding)Accepted forms reviewedForms completedAccepted Forms completed Pending ReviewFinal Dates
07/02/2022    07/02/2022
18/02/2022    18/02/2022
23/02/2022    23/02/2022
23/02/2022    23/02/2022
Blank11/03/2022   11/03/2022
Blank11/03/2022   11/03/2022
Blank11/03/2022   11/03/2022
BlankBlank11/05/2022  11/05/2022
BlankBlank11/05/2022  11/05/2022
BlankBlank11/05/2022  11/05/2022
BlankBlankBlank24/02/2022 24/02/2022
BlankBlankBlankBlank05/04/202205/04/2022
 
Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

hi,

It is easier to solve this in the Query Editor.  This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Offer Accepted", type date}, {"Offer Accepted (On Boarding)", type date}, {"Accepted forms reviewed", type date}, {"Forms completed", type date}, {"Accepted Forms completed Pending Review", type date}}),
    Custom1 = Table.AddColumn(#"Changed Type","Final date", each List.Min(Record.ToList(_)), type date)
in
    Custom1

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Ashish_Mathur Thank you. Can i do this as custom column as in my other table i have other columns as well

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      3 years ago

      Hi,

      See if this M code works

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Offer Accepted", type date}, {"Offer Accepted (On Boarding)", type date}, {"Accepted forms reviewed", type date}, {"Forms completed", type date}, {"Accepted Forms completed Pending Review", type date}}),
    Custom1 = Table.AddColumn(#"Changed Type","Final date", each List.Min(List.Skip(Record.ToList(_),2)), type date)
in
    Custom1
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Thank you. It worked, You're a saviour 🙂