Forum Discussion
Need Help in Formula IF(A1=A2,AC1,AB2)
- 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 nullExample 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 addCalcStartDateExample output:
Pete
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
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
- KuntalSingh3 years agoHelper V
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_Pete3 years agoSuper User
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
- KuntalSingh3 years agoHelper V
2000002705 28-Mar-2022 18:18:05 28-Mar-2022 18:19:04 28-Mar-2022 18:18:05 2000002705 29-Mar-2022 12:31:55 29-Mar-2022 12:38:18 28-Mar-2022 18:19:04 2000002705 28-Dec-2022 14:38:28 28-Dec-2022 14:38:30 29-Mar-2022 12:38:18 2000002705 28-Dec-2022 14:38:30 28-Dec-2022 14:38:30 28-Dec-2022 14:38:30 2000002705 28-Dec-2022 14:38:33 28-Dec-2022 14:38:34 28-Dec-2022 14:38:30 2000002919 30-Mar-2022 14:43:44 30-Mar-2022 14:45:02 30-Mar-2022 14:43:44 2000002919 30-Mar-2022 15:56:19 30-Mar-2022 15:57:24 30-Mar-2022 14:45:02 2000002919 30-Mar-2022 15:57:49 30-Mar-2022 16:22:50 30-Mar-2022 15:57:24 2000002919 12-Nov-2022 17:00:16 12-Nov-2022 17:01:02 30-Mar-2022 16:22:50 2000002919 24-Nov-2022 14:57:36 24-Nov-2022 14:58:11 12-Nov-2022 17:01:02 2000002919 16-Dec-2022 12:28:26 16-Dec-2022 12:28:27 24-Nov-2022 14:58:11 2000009764 19-Apr-2022 18:51:18 19-Apr-2022 18:54:04 19-Apr-2022 18:51:18 2000009764 20-Apr-2022 13:35:18 20-Apr-2022 13:41:05 19-Apr-2022 18:54:04 2000009764 20-Apr-2022 13:43:39 20-Apr-2022 13:43:39 20-Apr-2022 13:41:05 2000009764 21-Apr-2022 08:08:51 21-Apr-2022 08:08:51 20-Apr-2022 13:43:39 2000009764 28-Jun-2022 18:09:29 28-Jun-2022 18:09:29 21-Apr-2022 08:08:51 2000009764 28-Jun-2022 18:09:32 28-Jun-2022 18:09:32 28-Jun-2022 18:09:29 2000009764 26-Dec-2022 09:06:49 26-Dec-2022 09:06:49 28-Jun-2022 18:09:32 2000009764 26-Dec-2022 09:26:36 26-Dec-2022 09:26:36 26-Dec-2022 09:06:49 2000010681 22-Apr-2022 18:08:28 22-Apr-2022 18:09:43 22-Apr-2022 18:08:28 2000010681 22-Apr-2022 18:14:17 22-Apr-2022 18:17:51 22-Apr-2022 18:09:43 2000010681 22-Apr-2022 18:19:26 22-Apr-2022 18:19:26 22-Apr-2022 18:17:51 2000010681 06-Jan-2023 12:36:35 06-Jan-2023 12:37:51 22-Apr-2022 18:19:26 2000010681 09-Jan-2023 09:34:26 09-Jan-2023 09:34:28 06-Jan-2023 12:37:51 2000010681 09-Jan-2023 09:34:30 00-Jan-1900 00:00:00 09-Jan-2023 09:34:28 2000011084 25-Apr-2022 16:33:01 26-Apr-2022 10:12:06 25-Apr-2022 16:33:01 2000011084 29-Apr-2022 19:43:58 29-Apr-2022 19:45:52 26-Apr-2022 10:12:06 2000011084 02-May-2022 09:20:49 02-May-2022 09:26:31 29-Apr-2022 19:45:52 2000011084 02-May-2022 09:27:57 02-May-2022 09:27:57 02-May-2022 09:26:31 2000011084 19-Dec-2022 17:48:02 19-Dec-2022 17:48:02 02-May-2022 09:27:57 2000011084 19-Dec-2022 17:48:05 19-Dec-2022 17:48:05 19-Dec-2022 17:48:02 2000011084 21-Dec-2022 16:40:17 21-Dec-2022 16:40:17 19-Dec-2022 17:48:05 2000011084 21-Dec-2022 16:40:20 21-Dec-2022 16:40:20 21-Dec-2022 16:40:17 2000011084 21-Dec-2022 16:40:21 00-Jan-1900 00:00:00 21-Dec-2022 16:40:20 2000014548 09-May-2022 11:15:56 09-May-2022 11:20:01 09-May-2022 11:15:56 2000014548 09-May-2022 11:21:46 09-May-2022 11:29:13 09-May-2022 11:20:01 2000014548 09-May-2022 11:30:14 09-May-2022 11:30:14 09-May-2022 11:29:13 2000014548 26-Dec-2022 09:41:32 26-Dec-2022 09:41:32 09-May-2022 11:30:14 2000014548 26-Dec-2022 17:01:25 26-Dec-2022 17:01:28 26-Dec-2022 09:41:32 2000014548 28-Dec-2022 13:42:22 28-Dec-2022 13:42:22 26-Dec-2022 17:01:28 2000014548 28-Dec-2022 13:42:59 28-Dec-2022 13:42:59 28-Dec-2022 13:42:22 2000016604 13-May-2022 13:19:31 13-May-2022 13:21:48 13-May-2022 13:19:31 2000016604 14-May-2022 10:18:13 14-May-2022 10:26:06 13-May-2022 13:21:48 2000016604 14-May-2022 10:27:02 14-May-2022 10:27:02 14-May-2022 10:26:06 2000016604 19-May-2022 13:15:30 19-May-2022 13:15:30 14-May-2022 10:27:02 2000016604 04-Jan-2023 16:44:39 04-Jan-2023 16:44:44 19-May-2022 13:15:30 2000016604 04-Jan-2023 16:44:45 00-Jan-1900 00:00:00 04-Jan-2023 16:44:44 2000018575 19-May-2022 17:27:04 19-May-2022 17:27:50 19-May-2022 17:27:04 2000018575 19-May-2022 18:03:02 19-May-2022 18:07:46 19-May-2022 17:27:50 2000018575 19-May-2022 18:08:49 19-May-2022 18:08:49 19-May-2022 18:07:46 2000018575 20-May-2022 08:00:13 20-May-2022 08:00:13 19-May-2022 18:08:49