Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi All,
I want to write excel formula IF(A1=A2,AC1,AB2) in Power BI query editor. Can someone please help.
Solved! Go to Solution.
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
Proud to be a Datanaut!
Hi @KuntalSingh ,
The correct M code syntax would be:
if [A1] = [A2] then [AC1] else [AB2]
Pete
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
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
Proud to be a Datanaut!
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 |
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 |
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
Proud to be a Datanaut!
Hi Pete,
Solution gives me correct data but ufortunately when click applied and closed andit will increase the size of table to 1000 times. Can you please suggest why it happen
What do you mean it increases the size of the table 1,000 times? Are you getting duplicated rows or are you talking about application rows as it loads?
Pete
Proud to be a Datanaut!
my sheet file size is 15MB and after adding this custom column when i click on close and apply button power bi load shows size more than 3 GB and it keeps on loading not able to save the changes in the table.
Ok. It sounds as though PQ is rescanning everything multiple times.
In your query, insert a step after #"Added Index" like this:
bufferStep = Table.Buffer(#"Added Index")
Then adjust your calculation step to this:
try
if [Document Id] = bufferStep[Document Id]{[Index] + 1} then [#"Actual End Date & Time"] else bufferStep[#"Actual Start Date & Time"]{[Index] + 1}
otherwise null
Note that I've updated the step references to point to the buffered table. This should prevent PQ having to constantly reload the same table for scans.
Pete
Proud to be a Datanaut!
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
It looks like you just need to put the ' in #"Added Custom" ' bit on the next row, rather than right after the last bracket.
Pete
Proud to be a Datanaut!
Like this
= 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")
Hi Dear,
Added the same 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")
and got error
No.
' in #"Added Custom" ' is nothing to do with the calculation at all, it's just a marker so Power query knows which step to show as the final table.
Select your #"Added Index" step in the query list, then just add this as a new custom column:
try
if [Document Id] = #"Added Index"[Document Id]{[Index] + 1} then [#"Actual End Date & Time"] else #"Added Index"[#"Actual Start Date & Time"]{[Index] + 1}
otherwise null
Power Query will sort out all the other references for you.
Pete
Proud to be a Datanaut!
My table name is InvoiceLife_Cycle and mention below the the table structure and I want to get the new date into new colunm name start date. I am new to power bi so please help me in detail.
this is my table
Full Name | Document Id | Accounting Document No. | Company Code | Company Name | Vendor | Vendor Name | DP Document Type | Document Status Description | Fiscal Year | Cycle Time | Text | Process Type | Responsible Party | User Mapping Object ID | Actual Start Date of Process | Actual Start Time of Process | Actual End Date of Process | Actual End Time of Process | Processing Time (Sec.) | Index |
PIYUSH VRAJLAL THAKKAR AM(MAT) | 2000010681 | Undefined | In Process | Validate GST TDS (PO) | RECEIVER | 58877 | 18-Jan-23 | 15:02:10 | 00:00:00 | 181323 | 0 | |||||||||
VIM FI user | 2000010681 | Document Registered | BC Inbound | VIM_FB_BKG | ######## | 18:04:32 | ######## | 18:04:32 | 0 | 1 | ||||||||||
FI_BKG | 2000010681 | Processing Archiving | Early Archiving | FI_BKG | ######## | 18:04:32 | ######## | 18:07:00 | 148 | 2 | ||||||||||
VIM_BKG | 2000010681 | Extraction Completed | Update status | VIM_BKG | ######## | 18:07:01 | ######## | 18:08:28 | 87 | 3 | ||||||||||
FI_BKG | 2000010681 | Ready for Validation | BC Inbound | FI_BKG | ######## | 18:08:28 | ######## | 18:09:43 | 75 | 4 | ||||||||||
Bipin Rai | 2000010681 | Validation Complete | Update status | UCON900003 | ######## | 18:14:17 | ######## | 18:17:51 | 214 | 5 |
Full Name | Document Id | Accounting Document No. | Company Code | Company Name | Vendor | Vendor Name | DP Document Type | Document Status Description | Fiscal Year | Cycle Time | Text | Process Type | Responsible Party | User Mapping Object ID | Actual Start Date of Process | Actual Start Time of Process | Actual End Date of Process | Actual End Time of Process | Processing Time (Sec.) | Index |
PIYUSH VRAJLAL THAKKAR AM(MAT) | 2000010681 | 0 | ||||||||||||||||||
1 | ||||||||||||||||||||
2 | ||||||||||||||||||||
3 | ||||||||||||||||||||
4 | ||||||||||||||||||||
5 | ||||||||||||||||||||
Undefined | 6 | |||||||||||||||||||
In Process | Validate GST TDS (PO) | RECEIVER | 58877 | 18-Jan-23 | 15:02:10 | 7 | ||||||||||||||
00:00:00 | 181323 | 0 | 8 | |||||||||||||||||
VIM FI user | 2000010681 | 9 | ||||||||||||||||||
10 | ||||||||||||||||||||
Document Registered | BC Inbound | |||||||||||||||||||
VIM_FB_BKG | ######## | 18:04:32 | ######## | 18:04:32 | 0 | 1 | ||||||||||||||
FI_BKG | 2000010681 | |||||||||||||||||||
Processing Archiving | ||||||||||||||||||||
Early Archiving | ||||||||||||||||||||
FI_BKG | ######## | 18:04:32 | ######## | 18:07:00 | 148 | 2 | ||||||||||||||
VIM_BKG | 2000010681 | |||||||||||||||||||
Extraction Completed | Update status | |||||||||||||||||||
VIM_BKG | ######## | 18:07:01 | ######## | 18:08:28 | 87 | 3 | ||||||||||||||
FI_BKG | 2000010681 | |||||||||||||||||||
Ready for Validation | ||||||||||||||||||||
BC Inbound | ||||||||||||||||||||
FI_BKG | ######## | 18:08:28 | ######## | 18:09:43 | 75 | 4 |
Hello @KuntalSingh
You can use conditional column option from "Add Column"
Let me know if this helps !
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
58 | |
27 | |
17 | |
13 |