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

Get 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

Reply
KuntalSingh
Helper V
Helper V

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

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

TimeTime&DurationDateDuration CNTDateTimeDurationExtExt2LineInfoNumberStatusCall End TimeFinal Call End Time
08:47:1708:47:17 00:00:0007-Jul-2300:00:0001662 IC07/07/2023 08:47:17 00:00:00555555160C918700649925Call not attended08:47:1708:47:17
09:10:4809:10:48 00:00:0007-Jul-2300:00:0001667 IC07/07/2023 09:10:48 00:00:00555555100C919935006161Call not attended09:10:4808:47:17
09:27:1309:27:13 00:01:0107-Jul-2300:01:01181669 IC07/07/2023 09:27:13 00:01:01555555130 917979916997Call attended09:28:1409:28:14
09:44:0709:44:07 00:03:0507-Jul-2300:03:05371670 IC07/07/2023 09:44:07 00:03:05555555160 1206748568Call attended09:47:1209:47:12
09:48:4809:48:48 00:02:5307-Jul-2300:02:53811676 IC07/07/2023 09:48:48 00:02:53555555130 919557311610Call attended09:51:4109:51:41
09:50:0809:50:08 00:00:0007-Jul-2300:00:0001672 IC07/07/2023 09:50:08 00:00:00555 160C919599395796Call not attended09:50:0809:51:41
09:50:0909:50:09 00:00:0007-Jul-2300:00:0001673 IC07/07/2023 09:50:09 00:00:00555555160C919599395796Call not attended09:50:0909:51:41
09:50:1709:50:17 00:00:0007-Jul-2300:00:0001675 IC07/07/2023 09:50:17 00:00:00555555100C919599395796Call not attended09:50:1709:51:41
09:50:1709:50:17 00:00:0007-Jul-2300:00:0001674 IC07/07/2023 09:50:17 00:00:00555 100C919599395796Call not attended09:50:1709:51:41
10:02:1710:02:17 00:01:1007-Jul-2300:01:10851685 IC07/07/2023 10:02:17 00:01:10555555100 919999916126Call attended10:03:2710:03:27
10:05:2510:05:25 00:01:1207-Jul-2300:01:12331686 IC07/07/2023 10:05:25 00:01:12555555130 918318293099Call attended10:06:3710:06:37
10:10:2710:10:27 00:00:3907-Jul-2300:00:39891688 IC07/07/2023 10:10:27 00:00:39555555100 918080319175Call attended10:11:0610:11:06
10:11:3310:11:33 00:00:5207-Jul-2300:00:52521689 IC07/07/2023 10:11:33 00:00:52555555130 919211133310Call attended10:12:2510:12:25
10:12:5010:12:50 00:00:0007-Jul-2300:00:0001691 IC07/07/2023 10:12:50 00:00:00555555100C918178963703Call not attended10:12:5010:12:25
10:12:5010:12:50 00:00:0007-Jul-2300:00:0001690 IC07/07/2023 10:12:50 00:00:00555 100C918178963703Call not attended10:12:5010:12:25
10:13:2510:13:25 00:00:4807-Jul-2300:00:48561692 IC07/07/2023 10:13:25 00:00:48555 160C919599395796Call attended10:14:1310:14:13
10:13:2610:13:26 00:01:1507-Jul-2300:01:15561694 IC07/07/2023 10:13:26 00:01:15555555130 918178963703Call attended10:14:4110:14:41
10:15:5210:15:52 00:00:0007-Jul-2300:00:0001699 IC07/07/2023 10:15:52 00:00:00555555160C919058151380Call not attended10:15:5210:14:41
10:16:2110:16:21 00:06:3207-Jul-2300:06:3241730 IC07/07/2023 10:16:21 00:06:32555555131 919058151380Call attended10:22:5310:22:53
10:17:2610:17:26 00:00:0007-Jul-2300:00:0001705 IC07/07/2023 10:17:26 00:00:00555555130C919810015085Call not attended10:17:2610:22:53
10:17:2610:17:26 00:00:0007-Jul-2300:00:0001704 IC07/07/2023 10:17:26 00:00:00555 130C919810015085Call not attended10:17:2610:22:53
10:18:1610:18:16 00:00:0007-Jul-2300:00:0001709 IC07/07/2023 10:18:16 00:00:00555555100C919342679053Call not attended10:18:1610:22:53
10:18:1610:18:16 00:00:0007-Jul-2300:00:0001708 IC07/07/2023 10:18:16 00:00:00555 100C919342679053Call not attended10:18:1610:22:53
10:18:5410:18:54 00:00:4007-Jul-2300:00:40631710 IC07/07/2023 10:18:54 00:00:40555 161C919315876917Call attended10:19:3410:19:34
10:19:4310:19:43 00:00:0007-Jul-2300:00:0001713 IC07/07/2023 10:19:43 00:00:00555555161C918849891594Call not attended10:19:4310:19:34
1 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:

BA_Pete_0-1689936584828.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

4 REPLIES 4
KuntalSingh
Helper V
Helper V

Thanks Pete,

 

Its working 🙂

BA_Pete
Super User
Super User

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



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

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.

KuntalSingh_0-1689917835218.png

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:

BA_Pete_0-1689936584828.png

 

Pete



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

Proud to be a Datanaut!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.