Forum Discussion
KuntalSingh
3 years agoHelper V
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.
- 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
BA_Pete
3 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
KuntalSingh
3 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 |