Getting error trying to create a custom column in the transformation

[jamaicawi]

I have a report the data is structured (in Excel) as per example:

BuildingSite	EstimatedRepairDate	ActualRepairDate	EstimatedNumberofOffices
Northeast	8/1/2022	8/12/2022	5
Southeast	9/10/2022	9/2/222	10
Central/midwest	9/1/2023		50
Southeast	7/1/2021		9
Southwest	9/5/2022	9/5/2022	2
West	8/1/2022		3

I tried creating a custom column that will calculate the completed number of offices based on ActualRepairDate is null

= Table.AddColumn("Completed Business Sites", each if [#"ActualRepairDate"] <> null then [#"EstimatedNumberofOffices"] else 0)

I'm getting error which is "Token Comma expected". When I click on show error, it really does not show anything. However the red line is right on [#"EstimatedNumberofOffices"] line.

I'm stumped and struggling with this.. Appreciate your help.

[davehus]

You're most welcome. 😊

[davehus · Accepted solution]

Hi @jamaicawi ,

Try using the conditional column as below.

Hope this helps.

Did I help you today? Please accept my solution and hit the Kudos button.

[jamaicawi]

Hi davehus,

I wrote it into the custom column.

[davehus]

Hi @jamaicawi , is the measure above what you've actually written into the custom column or is it a copy from advanced editor?

Did I help you today? Please accept my solution and hit the Kudos button.

[jamaicawi]

You have helped! Thank you so much. I have accepted your solution. It worked. Now I know how to do this going forward. Thank you again!!