cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Giovanni-PBI Regular Visitor
Regular Visitor

How to extract the ID numbers from complex URL strings

Hi,
@ImkeF
@Zubair_Muhammad

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

VAR IndividualPageResult
Var Client A1 /vacancies/106624-data-scientist106624
Var Client A1 /404?aspxerrorpath=/vacancies/101360-hr-employee/apply101360
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-kw121511
Var Client B2/en/job/softwareengineer-vrt-nws-120191120191
Var Client C1 /en/vacancies/119521/apply119521
Var Client C2/en/vacancies/119527?fbclid=Iw25Ove7mD1Xj9W2gsjG0sLWUz119527
Var Client C3/en/vacancies/115181115181


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 Smiley Happy 

2 REPLIES 2
Community Support Team
Community Support Team

Re: How to extract the ID numbers from complex URL strings

Hi @Giovanni-PBI

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

 

Giovanni-PBI Regular Visitor
Regular Visitor

Re: How to extract the ID numbers from complex URL strings

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 Smiley Happy. 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?