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 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
| 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 |
- KuntalSingh3 years agoHelper V
Document IdFull NameActual Start Date & TimeActual End Date & TimeEntityStart Date
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 - BA_Pete3 years agoSuper User
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
- KuntalSingh3 years agoHelper V
Hi Pete,
I tried the mention below code
= Table.AddColumn(#"Added Index", "Custom", each
try
if [Document Id] = #"Added Index"[Document Id]{[Index] + 1}
then [Actual End Date of Process]
else #"Added Index"[Actual Start Date of Process]{[Index] + 1}
otherwise null
)in #"Added Custom"Got this error. Can you please check and help