none
Multiple/Nested If Then Else statement or recursive function RRS feed

  • Question

  • 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 other better way to do loop this, please share to me. Thanks in advance

    let

    A_ = Excel.Workbook(Web.Contents("https://companysalesdotcom:/", [RelativePath="timeReportHourly09_89605968_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
    B_ = Excel.Workbook(Web.Contents("https://companysalesdotcom:/", [RelativePath="timeReportHourly10_89605970_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
    C_ = Excel.Workbook(Web.Contents("https://companysalesdotcom:/", [RelativePath="timeReportHourly11_89605972_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
    D_ = Excel.Workbook(Web.Contents("https://companysalesdotcom:/", [RelativePath="timeReportHourly12_89605974_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
    E_ = Excel.Workbook(Web.Contents("https://companysalesdotcom:/", [RelativePath="timeReportHourly13_89605976_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
    F_ = Excel.Workbook(Web.Contents("https://companysalesdotcom:/", [RelativePath="timeReportHourly14_89605978_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
    G_ = Excel.Workbook(Web.Contents("https://companysalesdotcom:/", [RelativePath="timeReportHourly15_89605980_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),
    H_ = Excel.Workbook(Web.Contents("https://companysalesdotcom:/", [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

                     
    Tuesday, June 2, 2020 10:12 PM

Answers

  • your sequence of if ... then ... else is not logical:
    if H_= null then G_ ... What if G_= null. The next else if will never trigger. It does not look right.

    In addition,  I guess you want to test if the file exists, not if it is equal to null. It is not the same.

    I would rather try:

    let

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

    Source = if not (try H_) [HasError] then H_ else if not (try G_) [HasError] then G_ else if not (try F_) [HasError] then F_ else if not (try E_) [HasError] then E_ else if not (try D_) [HasError] then D_ else if not (try C_) [HasError] then C_ else if not (try B_) [HasError] then B_ else if not (try A_) [HasError] then A_ else null in Source


    • Marked as answer by marc_hll Thursday, June 4, 2020 2:33 PM
    • Edited by anthony34 Saturday, June 6, 2020 6:47 PM
    Wednesday, June 3, 2020 6:16 AM