if custom column formula using M Language

[SkorpionAAM]

I have table where i used 

this funcation which working fine 

if [operation_photo] = "" then "" else "***/1/layers/3633601/features/"&[ogc_fid]&"/"&"operation_photo/"&[operation_photo]&"/"&"?api_key=***"

but problem is in [operation_photo] column have some links like this http://Yahoo.com

which is correct so i need that method if there is proper link it like this http://Yahoo.com its give me this  and if not it will do this 

if [operation_photo] = "" then "" else "***/1/layers/3633601/features/"&[ogc_fid]&"/"&"operation_photo/"&[operation_photo]&"/"&"?api_key=***"

because at the moment this giving me this

https://*******/1/layers/3633601/features/10/operation_photo/https://dev.****.com/imglist/Storage/
02_747.jpg/?api_key=********

its should be like this

https://dev.****.com/imglist/Storage/02_747.jpg

[v-easonf-msft · Accepted solution]

Hi， @SkorpionAAM 

Take a try to  change your M code as below:

= Table.AddColumn(#"Changed Type", "Custom", each if [operation_photo] = "" then "" else if Text.Contains([operation_photo], "https://") then [operation_photo]  else "***/1/layers/3633601/features/"&[ogc_fid]&"/"&"operation_photo/"&[operation_photo]&"/"&"?api_key=***")

Please check my test pbix file for more details.

If it doesn't meet your requirement,please feel free to ask.

Best Regards,
Community Support Team _ Eason

[PhilipTreacy]

Hi @SkorpionAAM 

Sorry no I don't get your point that's why I asked if you could provide some sample data and examples of expected results.

Phil

[SkorpionAAM]

So output will come like this 

https://*****/1/layers/3633601/features/"&[ogc_fid]&"/"&"operation_photo/"&[operation_photo]&"/"&"?api_key=*****

https://dev.tecsolutiongroup.com/imglist/Storage/02_196.jpg

and im usuing this M condition 

if [operation_photo] = "" then "" else "https://*****/1/layers/3633601/features/"&[ogc_fid]&"/"&"operation_photo/"&[operation_photo]&"/"&"?api_key=*****"

[PhilipTreacy]

Hi @SkorpionAAM 

This isn't clear.  Please show some sample data in a a table and the expected result.

But I think what you are saying is to return the [operation_photo] URL when the value in that column isn't null

if [operation_photo] = null then null else [operation_photo]

but all you will end up with is a dupliacte of the [operation_photo] column, so that can't be right?

Regards

Phil

[SkorpionAAM]

see the Image which dont have links i create with this Condition 

if [operation_photo] = "" then "" else "https://*****/1/layers/3633601/features/"&[ogc_fid]&"/"&"operation_photo/"&[operation_photo]&"/"&"?api_key=*****"

now problem is who have already link it giving me like this 

https://****/1/layers/3633601/features/6/operation_photo/https://dev.tecsolutiongroup.com/imglist/Storage/
02_196.jpg/?api_key=***

i want who have already link thn dont concentare just give e result as it is if its not a link thn concentre

i think yo get my point