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