Forum Discussion

KuntalSingh's avatar
KuntalSingh
Helper V
3 years ago
Solved

Need Help in Formula IF(A1=A2,AC1,AB2)

Hi All,

 

I want to write excel formula IF(A1=A2,AC1,AB2) in Power BI query editor. Can someone please help.

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    That's perfect, thanks.

     

    You need to sort the data in the order that you want it to be evaluated, add an index column starting from zero, then add a new custom column with the following calculation:

     

    try
if [Document Id] = previousStepName[Document Id]{[Index] + 1} then [#"Actual End Date & Time"] else previousStepName[#"Actual Start Date & Time"]{[Index] + 1}
otherwise null

     

    Example working query:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZfLqtwwDIZf5TDrE5Dku3eFrg60L3CYxaF025ZCC337KnYycWIp8Qyz+pD/3xdZct7fbwTzjwK42+uN4vTl4/dEQPSCMfNfxCmD1aLvr0fN1ERRNpidhOfxmlWvGafP378tUXYeTFHEBjSrAc06+Ak8omlkbAc0EyaOMtCsxmZrsrUCdhlIiz7XdNn5LOKQSbW61AzZdthnouxAs+o0kaavP/8uUSEDZPQCRmHtq1WnSbYZbGdn4wXMSYOaVT9P35wkcXJm8jIOmtWmmYKfdx3T9OnXdjMc1gtzxLbeTTG60yRookw2brmEe2yxlgHR6kqTM86kcYz7GrJq4hYFXGbmBZ1gyarXjNPbnx+P1UDKlE6wZDWgaegZTNI8m6ThEPD1HmlYsrrSJL/k/BCuVg9NBB/LrtMuO2CtyQec+Dy06CtNvhwYBByWc5esLjVTvZuDOOzOfdUEP719lF03pcPwxjkBy/OsVr1m2gbPx2jrhAQcNasBzdLMAAvGBPDCNbX8NatNEyGWXHLNanjhhotiTZoNc50mzhstutdsK818jFwUBcx9gjSrThOIu8G/LbmhXpgj5jmhZnWpybsexnGx6jS5ym5NgptmrM1MwaLViKZ7Cgtr54q4RXFJgOVuyli0GtAkeAajsHZhMOo5L1ptmtbZWG/GY9eRW+v8ZOoxpxigFn2lSZitpJkyGs3qStPwDtlhXK06zUM34I5tSMei1almfVWRk7E6g16zfU3zK4DfW3SCJasBTZeewdTO0/v6UjPNDpm5G5QCdMBzOkQtute0TRTMX2YlaQ6YC1CpyaLVlSZXmlqXxrDf1+RVM+1X42o/0rBk1WmCbToh32BbH5o9tuoMBjStO+mbktWmGV1wR+dQVtNNqGDXbckafa7JJdY8escOh1paRKtLzVj75iAuVp0mQdO1YvmOMzoWre73/w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document Id" = _t, #"Actual Start Date & Time" = _t, #"Actual End Date & Time" = _t, #"Entity Start Date" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Document Id", Int64.Type}, {"Actual Start Date & Time", type datetime}, {"Actual End Date & Time", type datetime}, {"Entity Start Date", type datetime}}),

    sortRows = Table.Sort(chgTypes,{{"Document Id", Order.Ascending}, {"Actual Start Date & Time", Order.Ascending}}),
    addIndex0 = Table.AddIndexColumn(sortRows, "Index", 0, 1, Int64.Type),
    addCalcStartDate =
    Table.AddColumn(addIndex0, "calcStartDate", each
        try
            if [Document Id] = addIndex0[Document Id]{[Index] + 1}
            then [#"Actual End Date & Time"]
            else addIndex0[#"Actual Start Date & Time"]{[Index] + 1}
        otherwise null
    )

in
    addCalcStartDate

     

    Example output:

     

    Pete

22 Replies

    • KuntalSingh's avatar
      KuntalSingh
      Helper V
      3 years ago

      Hi,

       

      Thanks for your suggestion.

      I need to mention below logic 

      = Table.AddColumn(#"Renamed Columns3", "Custom", each if[Document Id1]=[Document Id2]then[#"Actual End Date & Time1"]else[#"Actual Start Date & Time2"])

    • KuntalSingh's avatar
      KuntalSingh
      Helper V
      3 years ago

      Hi Jadhav,

       

      I need to implement excel formula =IF(Z1=Z2,AC1,AB2) into Power BI and below is my data 

       

    • KuntalSingh's avatar
      KuntalSingh
      Helper V
      3 years ago

      Hi Pete,

       

      Thanks for your prompt reply I got error while implement the same logic 

      = Table.AddColumn(#"Renamed Columns3", "Custom", each if[Document Id1]=[DocumentId2]then[#"Actual End Date & Time1"]else[#"Actual Start Date & Time2"])

      Can you please helm me to correct the above code

       

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        Try the following instead. I've just added spaces around the IF clauses:

        = Table.AddColumn(#"Renamed Columns3", "Custom", each if [Document Id1]=[DocumentId2] then [#"Actual End Date & Time1"] else [#"Actual Start Date & Time2"])

         

        If that doesn't work, then you'll need to let me know what the error you're getting says.

         

        Pete