Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to 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
Proud to be a Datanaut!
Thanks Pete,
Its working 🙂
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
Proud to be a Datanaut!
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"
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
Proud to be a Datanaut!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
29 | |
17 | |
11 | |
10 | |
8 |
User | Count |
---|---|
42 | |
24 | |
21 | |
13 | |
11 |