Need to help in Counting countries separated by commas excluding empty row

[aishaakhter18]

Hi I am trying to create a calculation for the col above, first i tried below option

 Option 1

Countires Count updated = LEN('PID & HVT Metrics Tracker'[3 Col Countries]) - LEN(SUBSTITUTE('PID & HVT Metrics Tracker'[3 Col Countries],",",""))+1

Then use this option in power query 

+List.NonNullCount(Text.Split([3 Col Countries],","))

but both are giving me same result and count the the empty row too as you can see in the attached screen, how should i fix it?

Thanks

[aishaakhter18]

Thanks it worked, greatly appreciated 

[aishaakhter18]

Thanks alot , it worked 

[tamerj1]

Hi @aishaakhter18 

please try

Countires Count =
PATHLENGTH (
SUBSTITUTE ( 'PID & HVT Metrics Tracker'[3 Col Countries], ",", "|" )
)

[aishaakhter18]

Thanks for getting back but still counting the empty row, 😞

[tamerj1 · Accepted solution]

@aishaakhter18 

Please try

Countires Count =
PATHLENGTH (
SUBSTITUTE (
IF (
'PID & HVT Metrics Tracker'[3 Col Countries] = "",
BLANK (),
'PID & HVT Metrics Tracker'[3 Col Countries]
),
",",
"|"
)
)