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
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
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")
- BA_Pete3 years agoSuper User
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 nullPower Query will sort out all the other references for you.
Pete
- KuntalSingh3 years agoHelper V
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
- KuntalSingh3 years agoHelper V
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 - KuntalSingh3 years agoHelper V
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