Multiple/Nested If Then Else statement or recursive function

[Anonymous]

Hello there,

Can someone help me with below's query? It is not working, it doesn't roll to the next if statement.

Or if anyone has a better way to do loop this, please share to me. Thanks in advance

let

A_ = Excel.Workbook(Web.Contents("https://companysales.com:/", [RelativePath="timeReportHourly09_89605968_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
B_ = Excel.Workbook(Web.Contents("https://companysales.com:/", [RelativePath="timeReportHourly10_89605970_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
C_ = Excel.Workbook(Web.Contents("https://companysales.com:/", [RelativePath="timeReportHourly11_89605972_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
D_ = Excel.Workbook(Web.Contents("https://companysales.com:/", [RelativePath="timeReportHourly12_89605974_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
E_ = Excel.Workbook(Web.Contents("https://companysales.com:/", [RelativePath="timeReportHourly13_89605976_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
F_ = Excel.Workbook(Web.Contents("https://companysales.com:/", [RelativePath="timeReportHourly14_89605978_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
G_ = Excel.Workbook(Web.Contents("https://companysales.com:/", [RelativePath="timeReportHourly15_89605980_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
H_ = Excel.Workbook(Web.Contents("https://companysales.com:/", [RelativePath="timeReportHourly16_89605982_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),

Source =

 if H_ = null
 then G_
 else
        if G_ = null
        then F_
        else
              if F_ = null
              then E_
              else
                    if E_ = null
                    then D_
                    else
                          if D_ = null
                          then C_
                          else
                                if C_ = null
                                then B_
                                else
                                      if B_ = null
                                      then A_
                                      else H_

in
Source

[v-eachen-msft]

Hi @Anonymous ,

Your A_ or other tables must return a table which is not null.

So you need to use the data in them. Here is the codes for your reference. Use Table.IsEmpty() to get a True/False result.

if 
Table.IsEmpty(A_[Data]{0}) 
then B_ 
else if  Table.IsEmpty(B_[Data]{0}) 
then C_

[mahoneypat]

You can use the SWITCH() function to more easily handle nested if statements (and SWITCH(TRUE() ... where you are evaluating different expressions).  In your case though, I wouldn't use IF or SWITCH.  I don't know the logic exactly, but you should be able to get there with the use of sorting and maybe a Table.RowCount of the returned tables to see which ones are null (and then filter them out).  Basically sort them in the desired priority order and then filter out the nulls.  The top one should be the one you want, and you can reference it in a relative fashion (e.g., #"Previous Step"{0}[Content]).

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[AlexisOlson · Accepted solution]

@mahoneypat SWITCH is a DAX function (not M) so doesn't work in the query editor.

@Anonymous If it's returning an error, that is not the same as null. For error, you probably want to use the try _ otherwise _ construction that's described here: https://bengribaudo.com/blog/2020/01/15/4883/power-query-m-primer-part-15-error-handling

[mahoneypat]

You're right @AlexisOlson . I shouldn't have mentioned Switch in an M post.  I think of it whenever I see nested ifs.  The point I was trying to make was to potentially avoid nested ifs at all.  Similar to SharePoint.Folder/Combine & Edit, I would try to make all the calls and then use sorting and Table.RowCount to keep only the desired result.

Regards,

Pat

[AlexisOlson]

Have you checked that these are returning nulls where you expect them to?

Sidenote: I find it easier to read like this:

let
  [...],
  Source = if H_ = null then G_
      else if G_ = null then F_
      else if F_ = null then E_
      else if E_ = null then D_
      else if D_ = null then C_
      else if C_ = null then B_
      else if B_ = null then A_
      else H_
in
  Source

[Anonymous]

probably not, I get DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server

I changed it not equal to null also not working

if H_ <> null then H_
else if G_ <> null then G_
else if F_ <> null then F_
else if E_ <> null then E_
else if D_ <> null then D_
else if C_ <> null then C_
else if B_ <> null then B_
else if A_ <> null then A_
else null