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
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
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
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
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
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 |
Hello @KuntalSingh
You can use conditional column option from "Add Column"
Let me know if this helps !
Helpful resources
Power BI Monthly Update - September 2024
Check out the September 2024 Power BI update to learn about new features.
Microsoft Fabric & AI Learning Hackathon
Learn from experts, get hands-on experience, and win awesome prizes.
Fabric Community Update - September 2024
Find out what's new and trending in the Fabric Community.
