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

BA_Pete's avatar
BA_Pete
Super User
3 years ago

Hi KuntalSingh ,

 

The correct M code syntax would be:

if [A1] = [A2] then [AC1] else [AB2]

 

Pete

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

    • KuntalSingh's avatar
      KuntalSingh
      Helper V
      3 years ago

      This is my data

      and I want to apply excel formula IF(Z1=Z2,AC1,AB2) in power BI to get start date in column AE

       

      Used suggested query = Table.AddColumn(#"Reordered Column IBM / IOCL", "Start Date.1", each if[Document Id1]=[Document Id2]then[#"Actual End Date & Time1"]else[#"Actual Start Date & Time2"]) and it gives error

       

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

        Hi KuntalSingh ,

         

        Can you supply your example screenshot data in a copyable format please?

        You can either copy from Excel and paste here, or you can paste it into the Enter Data function on the Power Query Home tab and then copy/paste the M code from Advanced Editor into a code window ( </> button ) here.

         

        Pete