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
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
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