Forum Discussion
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 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 :)
2 Replies
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
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?
