Need Help on formula IF(M2="Call attended",N2,IF(ISNUMBER(O1),O1,N2))

[KuntalSingh]

I need to applied excel formula on column Final Call End Time= IF(M2="Call attended",N2,IF(ISNUMBER(O1),O1,N2)) where M2 is "Status" and N2 is "Call End Time" and O1 is Final Call End Time. Please help

Time

Time&Duration

Date

Duration

CNT

DateTimeDuration

Ext

Ext2

Line

Info

Number

Status

Call End Time

Final Call End Time

08:47:17

08:47:17 00:00:00

07-Jul-23

00:00:00

0

1662 IC

07/07/2023 08:47:17 00:00:00

555

555

160

C

918700649925

Call not attended

08:47:17

08:47:17

09:10:48

09:10:48 00:00:00

07-Jul-23

00:00:00

0

1667 IC

07/07/2023 09:10:48 00:00:00

555

555

100

C

919935006161

Call not attended

09:10:48

08:47:17

09:27:13

09:27:13 00:01:01

07-Jul-23

00:01:01

18

1669 IC

07/07/2023 09:27:13 00:01:01

555

555

130

917979916997

Call attended

09:28:14

09:28:14

09:44:07

09:44:07 00:03:05

07-Jul-23

00:03:05

37

1670 IC

07/07/2023 09:44:07 00:03:05

555

555

160

1206748568

Call attended

09:47:12

09:47:12

09:48:48

09:48:48 00:02:53

07-Jul-23

00:02:53

81

1676 IC

07/07/2023 09:48:48 00:02:53

555

555

130

919557311610

Call attended

09:51:41

09:51:41

09:50:08

09:50:08 00:00:00

07-Jul-23

00:00:00

0

1672 IC

07/07/2023 09:50:08 00:00:00

555

160

C

919599395796

Call not attended

09:50:08

09:51:41

09:50:09

09:50:09 00:00:00

07-Jul-23

00:00:00

0

1673 IC

07/07/2023 09:50:09 00:00:00

555

555

160

C

919599395796

Call not attended

09:50:09

09:51:41

09:50:17

09:50:17 00:00:00

07-Jul-23

00:00:00

0

1675 IC

07/07/2023 09:50:17 00:00:00

555

555

100

C

919599395796

Call not attended

09:50:17

09:51:41

09:50:17

09:50:17 00:00:00

07-Jul-23

00:00:00

0

1674 IC

07/07/2023 09:50:17 00:00:00

555

100

C

919599395796

Call not attended

09:50:17

09:51:41

10:02:17

10:02:17 00:01:10

07-Jul-23

00:01:10

85

1685 IC

07/07/2023 10:02:17 00:01:10

555

555

100

919999916126

Call attended

10:03:27

10:03:27

10:05:25

10:05:25 00:01:12

07-Jul-23

00:01:12

33

1686 IC

07/07/2023 10:05:25 00:01:12

555

555

130

918318293099

Call attended

10:06:37

10:06:37

10:10:27

10:10:27 00:00:39

07-Jul-23

00:00:39

89

1688 IC

07/07/2023 10:10:27 00:00:39

555

555

100

918080319175

Call attended

10:11:06

10:11:06

10:11:33

10:11:33 00:00:52

07-Jul-23

00:00:52

52

1689 IC

07/07/2023 10:11:33 00:00:52

555

555

130

919211133310

Call attended

10:12:25

10:12:25

10:12:50

10:12:50 00:00:00

07-Jul-23

00:00:00

0

1691 IC

07/07/2023 10:12:50 00:00:00

555

555

100

C

918178963703

Call not attended

10:12:50

10:12:25

10:12:50

10:12:50 00:00:00

07-Jul-23

00:00:00

0

1690 IC

07/07/2023 10:12:50 00:00:00

555

100

C

918178963703

Call not attended

10:12:50

10:12:25

10:13:25

10:13:25 00:00:48

07-Jul-23

00:00:48

56

1692 IC

07/07/2023 10:13:25 00:00:48

555

160

C

919599395796

Call attended

10:14:13

10:14:13

10:13:26

10:13:26 00:01:15

07-Jul-23

00:01:15

56

1694 IC

07/07/2023 10:13:26 00:01:15

555

555

130

918178963703

Call attended

10:14:41

10:14:41

10:15:52

10:15:52 00:00:00

07-Jul-23

00:00:00

0

1699 IC

07/07/2023 10:15:52 00:00:00

555

555

160

C

919058151380

Call not attended

10:15:52

10:14:41

10:16:21

10:16:21 00:06:32

07-Jul-23

00:06:32

4

1730 IC

07/07/2023 10:16:21 00:06:32

555

555

131

919058151380

Call attended

10:22:53

10:22:53

10:17:26

10:17:26 00:00:00

07-Jul-23

00:00:00

0

1705 IC

07/07/2023 10:17:26 00:00:00

555

555

130

C

919810015085

Call not attended

10:17:26

10:22:53

10:17:26

10:17:26 00:00:00

07-Jul-23

00:00:00

0

1704 IC

07/07/2023 10:17:26 00:00:00

555

130

C

919810015085

Call not attended

10:17:26

10:22:53

10:18:16

10:18:16 00:00:00

07-Jul-23

00:00:00

0

1709 IC

07/07/2023 10:18:16 00:00:00

555

555

100

C

919342679053

Call not attended

10:18:16

10:22:53

10:18:16

10:18:16 00:00:00

07-Jul-23

00:00:00

0

1708 IC

07/07/2023 10:18:16 00:00:00

555

100

C

919342679053

Call not attended

10:18:16

10:22:53

10:18:54

10:18:54 00:00:40

07-Jul-23

00:00:40

63

1710 IC

07/07/2023 10:18:54 00:00:40

555

161

C

919315876917

Call attended

10:19:34

10:19:34

10:19:43

10:19:43 00:00:00

07-Jul-23

00:00:00

0

1713 IC

07/07/2023 10:19:43 00:00:00

555

555

161

C

918849891594

Call not attended

10:19:43

10:19:34

[KuntalSingh]

Thanks Pete,

Its working 🙂

[BA_Pete]

Hi @KuntalSingh ,

Your formula translates into M code like this:

if [Status] = "Call attended" then [Call End Time]
else if Value.Is([Call End Time], Int64.Type) then [Final Call End Time]
else [Call End Time]

The only thing you might need to adjust would be the data type check: I've checked for integer value (Int64.Type), but you may need to change this to 'type number' instead.

Pete

[KuntalSingh]

Hi Pete,

Used the above mention code but did not give desired output. Ask is if Status is Call not attended then Final Call time should be last call attended time and if Status is  Call attended should have callend time.

let
Source = Folder.Files("C:\Users\08000S744\Box\Phone Calls Record\July_2023"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Id", type text}, {"Name", type text}, {"Kind", type text}, {"Data", type any}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Kind] = "Table")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14"}),
#"Filtered Rows2" = Table.SelectRows(#"Expanded Data", each not Text.Contains([Source.Name], "Summary")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Source.Name", "Id", "Name", "Kind", "Column11", "Column12", "Column13", "Column14"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Removed Columns1" = Table.RemoveColumns(#"Promoted Headers",{"Column8", "Column9", "Column10"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"4788 IC", "CNT"}, {"03/07/2023 05:37:27 00:00:00", "DateTimeDuration"}, {"555", "Ext"}, {"555_1", "Ext2"}, {"130", "Line"}, {"C", "Info"}, {"918969040624", "Number"}}),
#"Inserted Text Before Delimiter Date" = Table.AddColumn(#"Renamed Columns", "Text Before Delimiter", each Text.BeforeDelimiter([DateTimeDuration], " "), type text),
#"Filtered Rows" = Table.SelectRows(#"Inserted Text Before Delimiter Date", each ([Line] <> null)),
#"Renamed Date" = Table.RenameColumns(#"Filtered Rows",{{"Text Before Delimiter", "Date"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Date",{{"Date", type date}}),
#"Inserted Text After Delimiter Time" = Table.AddColumn(#"Changed Type1", "Text After Delimiter", each Text.AfterDelimiter([DateTimeDuration], " "), type text),
#"Inserted Text After Delimiter Duration" = Table.AddColumn(#"Inserted Text After Delimiter Time", "Text After Delimiter.1", each Text.AfterDelimiter([Text After Delimiter], " "), type text),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Text After Delimiter Duration",{{"Text After Delimiter.1", "Duration"}, {"Text After Delimiter", "Time"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Duration", type duration}}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type2", "Text Before Delimiter", each Text.BeforeDelimiter([Time], " "), type text),
#"Renamed Columns3" = Table.RenameColumns(#"Inserted Text Before Delimiter",{{"Time", "Time&Duration"}, {"Text Before Delimiter", "Time"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Time", type time}, {"Date", type date}}),
#"Inserted Last Characters" = Table.AddColumn(#"Changed Type3", "Last Characters", each Text.End(Text.From([Duration], "en-IN"), 2), type text),
#"Renamed Columns4" = Table.RenameColumns(#"Inserted Last Characters",{{"Last Characters", "DurationTime"}}),
#"Filtered Rows3" = Table.SelectRows(#"Renamed Columns4", each ([Ext] <> null)),
#"Changed Type4" = Table.TransformColumnTypes(#"Filtered Rows3",{{"DurationTime", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type4", "Status", each if [DurationTime] > 0 then "Call attended" else "Call not attended"),
#"Added Index" = Table.AddIndexColumn(#"Added Conditional Column", "Index", 1, 1, Int64.Type),
#"Changed Type6" = Table.TransformColumnTypes(#"Added Index",{{"Status", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type6", "CallEndTime", each [Time]+[Duration]),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Custom",{{"CallEndTime", type time}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type5", "FinalCall_End_Time", each if [Status] = "Call attended" then [CallEndTime]
else if Value.Is([CallEndTime], Int64.Type) then [FinalCall_End_Time]
else [CallEndTime])
in
#"Added Custom1"

[BA_Pete · Accepted solution]

This example replicates the logic of your Excel formula as simply as possible:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZfBTuUwDEV/pWINmjiO4zhbVjO/gFggwQ4xG+b/J0mTNm0cVMRD5eliys3BtZ306enOhOg4At/db3IxJpYrx/jhz7/3B4tZd+H0Dd7b5fdjuelXuqyxuGgeRLR9gs+R/EcCgY3xTsTmXzy+vL8vH38/l5fPz7eP17fXDqiXz/eJWSKY6EKOV3mdmUdmxePAbHZmEaREDR5mzB3bidmmH3C9pciyHqRLYa5hCCu0aNCDyQEaM/RSoFlYBLwIN+gjsA0RXC8rsHPR8BovsqyF0ZACXMPIBZiNBjyYjJWRgcEazy6QDzpuTqrtZcMNW00UWVaykVDBreEAK65Xcc8ms/wKESOkmjA6MEF00MsKTMm6Ahd5uYhZaTzFYyVdTm0nlIpYiMXPS7gnOxPLfotcJ8YZsVwZFVeZRWcG3m75xnhjmjB/Md7Mt5k3ttswu28wL7cihtIjJd5knUugEddwWJ9yGNOsmYxprs2Xv1Lv2Q26A4Yyayz3sgFTtFTjWba1rA6cw4gr8DgtNJPZtAgIwQoakQmwj8i9rMDpav9IkfV5oqg1UcJBVuCgAQ8mswwHEwxC2kZIB4a0/fheNmCIa8aqrGuRluEaLh8JeNzvNJPpPLYAgIjqPM5Gdnv0q2zAac6bLU7mctcJqLxnj9mkCMBBPLLBSd8NbLdgHrfoOfN5UvyIGPfsY+uZdmQaiEuY/Io8bnuaydVt74Tr1tPZJjtcv+P61uLaIaiGG+44izWT6ZwYcjwAl1PFJhswrd3R5PWK0Lvu7DHdow0FIMCwdZ1SEx3bgdlH2/6XLMt6afhpo6KG84EVGPUqPnsccwz7pBiYj7y2nvw22Xh5Lwpuz/NCjtmom93gMdZEzXFIXQhkwuyV6ch2I2a9jnXm5ZbE6WWkEWd5nViv5LPH9OSGznpOtfHVdNvZbsSsb9I683By+xkxue0Wcm2U6sQl7EtPMOjNdzbp5zF0yECB09apvZRmG4noetlw04sZbnGHlxMM4yuI5nEcbztvCE6CAIn7IsUHtsz8/B8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t, #"Time&Duration" = _t, Date = _t, Duration = _t, #" " = _t, CNT = _t, DateTimeDuration = _t, Ext = _t, Ext2 = _t, Line = _t, Info = _t, Number = _t, Status = _t, #"Call End Time" = _t, #"Final Call End Time" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Time", type time}, {"Time&Duration", type text}, {"Date", type date}, {"Duration", type time}, {" ", Int64.Type}, {"CNT", type text}, {"DateTimeDuration", type text}, {"Ext", Int64.Type}, {"Ext2", Int64.Type}, {"Line", Int64.Type}, {"Info", type text}, {"Number", Int64.Type}, {"Status", type text}, {"Call End Time", type time}, {"Final Call End Time", type time}}),
    sortCallEndTime = Table.Sort(chgTypes,{{"Call End Time", Order.Ascending}}),
    addIndex = Table.AddIndexColumn(sortCallEndTime, "Index", 0, 1, Int64.Type),
    addFinalCallEnd_calc =
    Table.AddColumn(
        addIndex,
        "finalCallEnd_calc",
        each if [Index] = 0 then [Call End Time]
        else if [Status] = "Call attended" then [Call End Time]
        else null
    ),
    fillDownFinalCallEnd = Table.FillDown(addFinalCallEnd_calc,{"finalCallEnd_calc"})
in
    fillDownFinalCallEnd

Summary:

-1- Add index column.

-2- Add column with following logic:

-- Check against index to see if first record in table. If yes then [Call End Time]

-- Else if call attended then [Call End Time]

-- Else null

-3- Fill Down new column

Example output:

Pete