Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
KuntalSingh
Helper V
Helper 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.

1 ACCEPTED 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:BA_Pete_0-1674030618494.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

22 REPLIES 22
BA_Pete
Super User
Super User

Hi @KuntalSingh ,

 

The correct M code syntax would be:

if [A1] = [A2] then [AC1] else [AB2]

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




This is my data

KuntalSingh_0-1673942127326.png

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

KuntalSingh_1-1673942288497.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




2000002705 28-Mar-2022 18:18:0528-Mar-2022 18:19:04 28-Mar-2022 18:18:05
2000002705 29-Mar-2022 12:31:5529-Mar-2022 12:38:18 28-Mar-2022 18:19:04
2000002705 28-Dec-2022 14:38:2828-Dec-2022 14:38:30 29-Mar-2022 12:38:18
2000002705 28-Dec-2022 14:38:3028-Dec-2022 14:38:30 28-Dec-2022 14:38:30
2000002705 28-Dec-2022 14:38:3328-Dec-2022 14:38:34 28-Dec-2022 14:38:30
2000002919 30-Mar-2022 14:43:4430-Mar-2022 14:45:02 30-Mar-2022 14:43:44
2000002919 30-Mar-2022 15:56:1930-Mar-2022 15:57:24 30-Mar-2022 14:45:02
2000002919 30-Mar-2022 15:57:4930-Mar-2022 16:22:50 30-Mar-2022 15:57:24
2000002919 12-Nov-2022 17:00:1612-Nov-2022 17:01:02 30-Mar-2022 16:22:50
2000002919 24-Nov-2022 14:57:3624-Nov-2022 14:58:11 12-Nov-2022 17:01:02
2000002919 16-Dec-2022 12:28:2616-Dec-2022 12:28:27 24-Nov-2022 14:58:11
2000009764 19-Apr-2022 18:51:1819-Apr-2022 18:54:04 19-Apr-2022 18:51:18
2000009764 20-Apr-2022 13:35:1820-Apr-2022 13:41:05 19-Apr-2022 18:54:04
2000009764 20-Apr-2022 13:43:3920-Apr-2022 13:43:39 20-Apr-2022 13:41:05
2000009764 21-Apr-2022 08:08:5121-Apr-2022 08:08:51 20-Apr-2022 13:43:39
2000009764 28-Jun-2022 18:09:2928-Jun-2022 18:09:29 21-Apr-2022 08:08:51
2000009764 28-Jun-2022 18:09:3228-Jun-2022 18:09:32 28-Jun-2022 18:09:29
2000009764 26-Dec-2022 09:06:4926-Dec-2022 09:06:49 28-Jun-2022 18:09:32
2000009764 26-Dec-2022 09:26:3626-Dec-2022 09:26:36 26-Dec-2022 09:06:49
2000010681 22-Apr-2022 18:08:2822-Apr-2022 18:09:43 22-Apr-2022 18:08:28
2000010681 22-Apr-2022 18:14:1722-Apr-2022 18:17:51 22-Apr-2022 18:09:43
2000010681 22-Apr-2022 18:19:2622-Apr-2022 18:19:26 22-Apr-2022 18:17:51
2000010681 06-Jan-2023 12:36:3506-Jan-2023 12:37:51 22-Apr-2022 18:19:26
2000010681 09-Jan-2023 09:34:2609-Jan-2023 09:34:28 06-Jan-2023 12:37:51
2000010681 09-Jan-2023 09:34:3000-Jan-1900 00:00:00 09-Jan-2023 09:34:28
2000011084 25-Apr-2022 16:33:0126-Apr-2022 10:12:06 25-Apr-2022 16:33:01
2000011084 29-Apr-2022 19:43:5829-Apr-2022 19:45:52 26-Apr-2022 10:12:06
2000011084 02-May-2022 09:20:4902-May-2022 09:26:31 29-Apr-2022 19:45:52
2000011084 02-May-2022 09:27:5702-May-2022 09:27:57 02-May-2022 09:26:31
2000011084 19-Dec-2022 17:48:0219-Dec-2022 17:48:02 02-May-2022 09:27:57
2000011084 19-Dec-2022 17:48:0519-Dec-2022 17:48:05 19-Dec-2022 17:48:02
2000011084 21-Dec-2022 16:40:1721-Dec-2022 16:40:17 19-Dec-2022 17:48:05
2000011084 21-Dec-2022 16:40:2021-Dec-2022 16:40:20 21-Dec-2022 16:40:17
2000011084 21-Dec-2022 16:40:2100-Jan-1900 00:00:00 21-Dec-2022 16:40:20
2000014548 09-May-2022 11:15:5609-May-2022 11:20:01 09-May-2022 11:15:56
2000014548 09-May-2022 11:21:4609-May-2022 11:29:13 09-May-2022 11:20:01
2000014548 09-May-2022 11:30:1409-May-2022 11:30:14 09-May-2022 11:29:13
2000014548 26-Dec-2022 09:41:3226-Dec-2022 09:41:32 09-May-2022 11:30:14
2000014548 26-Dec-2022 17:01:2526-Dec-2022 17:01:28 26-Dec-2022 09:41:32
2000014548 28-Dec-2022 13:42:2228-Dec-2022 13:42:22 26-Dec-2022 17:01:28
2000014548 28-Dec-2022 13:42:5928-Dec-2022 13:42:59 28-Dec-2022 13:42:22
2000016604 13-May-2022 13:19:3113-May-2022 13:21:48 13-May-2022 13:19:31
2000016604 14-May-2022 10:18:1314-May-2022 10:26:06 13-May-2022 13:21:48
2000016604 14-May-2022 10:27:0214-May-2022 10:27:02 14-May-2022 10:26:06
2000016604 19-May-2022 13:15:3019-May-2022 13:15:30 14-May-2022 10:27:02
2000016604 04-Jan-2023 16:44:3904-Jan-2023 16:44:44 19-May-2022 13:15:30
2000016604 04-Jan-2023 16:44:4500-Jan-1900 00:00:00 04-Jan-2023 16:44:44
2000018575 19-May-2022 17:27:0419-May-2022 17:27:50 19-May-2022 17:27:04
2000018575 19-May-2022 18:03:0219-May-2022 18:07:46 19-May-2022 17:27:50
2000018575 19-May-2022 18:08:4919-May-2022 18:08:49 19-May-2022 18:07:46
2000018575 20-May-2022 08:00:1320-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:0528-Mar-2022 18:19:04 28-Mar-2022 18:18:05
2000002705 29-Mar-2022 12:31:5529-Mar-2022 12:38:18 28-Mar-2022 18:19:04
2000002705 28-Dec-2022 14:38:2828-Dec-2022 14:38:30 29-Mar-2022 12:38:18
2000002705 28-Dec-2022 14:38:3028-Dec-2022 14:38:30 28-Dec-2022 14:38:30
2000002705 28-Dec-2022 14:38:3328-Dec-2022 14:38:34 28-Dec-2022 14:38:30
2000002919 30-Mar-2022 14:43:4430-Mar-2022 14:45:02 30-Mar-2022 14:43:44
2000002919 30-Mar-2022 15:56:1930-Mar-2022 15:57:24 30-Mar-2022 14:45:02
2000002919 30-Mar-2022 15:57:4930-Mar-2022 16:22:50 30-Mar-2022 15:57:24
2000002919 12-Nov-2022 17:00:1612-Nov-2022 17:01:02 30-Mar-2022 16:22:50
2000002919 24-Nov-2022 14:57:3624-Nov-2022 14:58:11 12-Nov-2022 17:01:02
2000002919 16-Dec-2022 12:28:2616-Dec-2022 12:28:27 24-Nov-2022 14:58:11
2000009764 19-Apr-2022 18:51:1819-Apr-2022 18:54:04 19-Apr-2022 18:51:18
2000009764 20-Apr-2022 13:35:1820-Apr-2022 13:41:05 19-Apr-2022 18:54:04
2000009764 20-Apr-2022 13:43:3920-Apr-2022 13:43:39 20-Apr-2022 13:41:05
2000009764 21-Apr-2022 08:08:5121-Apr-2022 08:08:51 20-Apr-2022 13:43:39
2000009764 28-Jun-2022 18:09:2928-Jun-2022 18:09:29 21-Apr-2022 08:08:51
2000009764 28-Jun-2022 18:09:3228-Jun-2022 18:09:32 28-Jun-2022 18:09:29
2000009764 26-Dec-2022 09:06:4926-Dec-2022 09:06:49 28-Jun-2022 18:09:32
2000009764 26-Dec-2022 09:26:3626-Dec-2022 09:26:36 26-Dec-2022 09:06:49
2000010681 22-Apr-2022 18:08:2822-Apr-2022 18:09:43 22-Apr-2022 18:08:28
2000010681 22-Apr-2022 18:14:1722-Apr-2022 18:17:51 22-Apr-2022 18:09:43
2000010681 22-Apr-2022 18:19:2622-Apr-2022 18:19:26 22-Apr-2022 18:17:51
2000010681 06-Jan-2023 12:36:3506-Jan-2023 12:37:51 22-Apr-2022 18:19:26
2000010681 09-Jan-2023 09:34:2609-Jan-2023 09:34:28 06-Jan-2023 12:37:51
2000010681 09-Jan-2023 09:34:3000-Jan-1900 00:00:00 09-Jan-2023 09:34:28
2000011084 25-Apr-2022 16:33:0126-Apr-2022 10:12:06 25-Apr-2022 16:33:01
2000011084 29-Apr-2022 19:43:5829-Apr-2022 19:45:52 26-Apr-2022 10:12:06
2000011084 02-May-2022 09:20:4902-May-2022 09:26:31 29-Apr-2022 19:45:52
2000011084 02-May-2022 09:27:5702-May-2022 09:27:57 02-May-2022 09:26:31
2000011084 19-Dec-2022 17:48:0219-Dec-2022 17:48:02 02-May-2022 09:27:57
2000011084 19-Dec-2022 17:48:0519-Dec-2022 17:48:05 19-Dec-2022 17:48:02
2000011084 21-Dec-2022 16:40:1721-Dec-2022 16:40:17 19-Dec-2022 17:48:05
2000011084 21-Dec-2022 16:40:2021-Dec-2022 16:40:20 21-Dec-2022 16:40:17
2000011084 21-Dec-2022 16:40:2100-Jan-1900 00:00:00 21-Dec-2022 16:40:20
2000014548 09-May-2022 11:15:5609-May-2022 11:20:01 09-May-2022 11:15:56
2000014548 09-May-2022 11:21:4609-May-2022 11:29:13 09-May-2022 11:20:01
2000014548 09-May-2022 11:30:1409-May-2022 11:30:14 09-May-2022 11:29:13
2000014548 26-Dec-2022 09:41:3226-Dec-2022 09:41:32 09-May-2022 11:30:14
2000014548 26-Dec-2022 17:01:2526-Dec-2022 17:01:28 26-Dec-2022 09:41:32
2000014548 28-Dec-2022 13:42:2228-Dec-2022 13:42:22 26-Dec-2022 17:01:28
2000014548 28-Dec-2022 13:42:5928-Dec-2022 13:42:59 28-Dec-2022 13:42:22
2000016604 13-May-2022 13:19:3113-May-2022 13:21:48 13-May-2022 13:19:31
2000016604 14-May-2022 10:18:1314-May-2022 10:26:06 13-May-2022 13:21:48
2000016604 14-May-2022 10:27:0214-May-2022 10:27:02 14-May-2022 10:26:06
2000016604 19-May-2022 13:15:3019-May-2022 13:15:30 14-May-2022 10:27:02
2000016604 04-Jan-2023 16:44:3904-Jan-2023 16:44:44 19-May-2022 13:15:30
2000016604 04-Jan-2023 16:44:4500-Jan-1900 00:00:00 04-Jan-2023 16:44:44
2000018575 19-May-2022 17:27:0419-May-2022 17:27:50 19-May-2022 17:27:04
2000018575 19-May-2022 18:03:0219-May-2022 18:07:46 19-May-2022 17:27:50
2000018575 19-May-2022 18:08:4919-May-2022 18:08:49 19-May-2022 18:07:46
2000018575 20-May-2022 08:00:1320-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:BA_Pete_0-1674030618494.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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

KuntalSingh_0-1675154902030.png

 

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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 

KuntalSingh_0-1675161845764.png

 

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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.

KuntalSingh_0-1675163306842.png

this is my table

Full NameDocument IdAccounting Document No.Company CodeCompany NameVendorVendor NameDP Document TypeDocument Status DescriptionFiscal YearCycle TimeTextProcess TypeResponsible PartyUser Mapping Object IDActual Start Date of ProcessActual Start Time of ProcessActual End Date of ProcessActual End Time of ProcessProcessing Time (Sec.)Index
PIYUSH VRAJLAL THAKKAR AM(MAT)2000010681      Undefined In ProcessValidate GST TDS (PO)RECEIVER5887718-Jan-2315:02:10 00:00:001813230
VIM FI user2000010681      Document RegisteredBC Inbound VIM_FB_BKG########18:04:32########18:04:3201
FI_BKG2000010681      Processing Archiving Early Archiving FI_BKG########18:04:32########18:07:001482
VIM_BKG2000010681      Extraction CompletedUpdate status VIM_BKG########18:07:01########18:08:28873
FI_BKG2000010681      Ready for Validation BC Inbound FI_BKG########18:08:28########18:09:43754
Bipin Rai2000010681      Validation Complete Update status UCON900003########18:14:17########18:17:512145

Full NameDocument IdAccounting Document No.Company CodeCompany NameVendorVendor NameDP Document TypeDocument Status DescriptionFiscal YearCycle TimeTextProcess TypeResponsible PartyUser Mapping Object IDActual Start Date of ProcessActual Start Time of ProcessActual End Date of ProcessActual End Time of ProcessProcessing Time (Sec.)Index
PIYUSH VRAJLAL THAKKAR AM(MAT)2000010681                  0
                    1
                    2
                    3
                    4
                    5
Undefined                   6
In ProcessValidate GST TDS (PO)RECEIVER5887718-Jan-2315:02:10              7
00:00:001813230                 8
VIM FI user2000010681                  9
                    10
                     
                     
                     
                     
Document RegisteredBC Inbound                   
VIM_FB_BKG########18:04:32########18:04:3201              
FI_BKG2000010681                   
                     
                     
                     
                     
                     
Processing Archiving                    
Early Archiving                    
FI_BKG########18:04:32########18:07:001482              
VIM_BKG2000010681                   
                     
                     
                     
                     
                     
Extraction CompletedUpdate status                   
VIM_BKG########18:07:01########18:08:28873              
FI_BKG2000010681                   
                     
                     
                     
                     
                     
Ready for Validation                    
BC Inbound                    
FI_BKG########18:08:28########18:09:43754              

Hello @KuntalSingh 

 

You can use conditional column option from "Add Column"

 

JadhavVarsha_13_1-1673875886370.png

 

Let me know if this helps !

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.