How to extract the ID numbers from complex URL strings

[Anonymous]

Hi,
@ImkeF
@Zubair_Muhammad

This is the full PBI problem I’m trying to solve. This is my data.

VAR Individual	Page	Result
Var Client A1	/vacancies/106624-data-scientist	106624
Var Client A1	/404?aspxerrorpath=/vacancies/101360-hr-employee/apply	101360
Var Client A2	/vacancies/groups/events--hospitality/
Var Client A2	/vacancies/groups/cosmetics/?fbclid=R3eEXwhyolvuxOMLfii
Var Client B1	/en/job/social-nurse-121511?fbclid=2BOtQuFefhPjUvq-kw	121511
Var Client B2	/en/job/softwareengineer-vrt-nws-120191	120191
Var Client C1	/en/vacancies/119521/apply	119521
Var Client C2	/en/vacancies/119527?fbclid=Iw25Ove7mD1Xj9W2gsjG0sLWUz	119527
Var Client C3	/en/vacancies/115181	115181

For every unique URL, I’m trying to return the ID in the result column.

Each client has a set of rules.

ID=

// Var Client A
1) Var Client A1 = Select (3 to 6 digit value) between / and -
2) Var Client A2 = IF Var Client A1 = a character THEN Blank
3) Var Client A = Var Client A2

// Var Client B
1) Var Client B1 = IF Page = Var Client A THEN Var Client A ELSE Select (3 to 6 digit value) between - and ?
2) Var Client B2 = IF Var Client B1 = blank THEN extract the (3 to 6 digit value) after the last -
3) Var Client B = Var Client B2

// Var Client C
1) Var Client C1 = IF Page = Var Client B THEN Var Client B ELSE Select (3 to 6 digit value) between / and /apply
2) Var Client C2 = IF Var Client C1 = blank THEN extract the (3 to 6 digit value) after the last /
3) Var Client C = Var Client C2

Var ID =Var Client C

Return Var ID

Am I thinking in the right/most efficient way?

Hopefully you can help 🙂 

[v-juanli-msft]

Hi @Anonymous

I don't understand 

"Var Client B1 = IF Page = Var Client A THEN Var Client A ELSE Select (3 to 6 digit value) between - and ?"

Page = Var Client A?

in the "Page" column, there isn't any value equal to "Var Client A" or equal to the "Page" cells for "Var Client A" row.

Best Regards

Maggie

[Anonymous]

Hi @v-juanli-msft,

Thanks for the reply

My goal is to extract each ID from the unique url strings.

To be more specific, it's more like this

1) Var Client_A1 = Select the (3 to 6 digit value) between "/" and "-" 
2) Var Client_A2 = IF Client_A1 = numeric THEN Client_A1 ELSE Blank
3) Var Client_A = Client_A2 

If I press Return Client_A1, it will fill the 106624 and the 101360 in the result column.
If I press Return Client_A2, it will also fill the blank in row 3 in the result column
Client_A returns the same result as Client_A2. I'm creating the Client_A variable, just to make the code more clean.

Eventually, if I return the last variable, then it should fill all the ID's in the result column.
That's the idea :). And the process I'm following.

I actually have a Dax script like this working. But it's too long.

So as a next step, I'm trying to find a better way to achieve the same result.

Maybe in M? My question is how can I return all the ID's in the list in the best/most efficient way possible?

Can you help me with it?