Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
@ImkeF
@Zubair_Muhammad
Now I’m trying to solve a slightly more complex situation. I’m trying to extract the 5 and 6 length ID numbers for UK and France.
This is my current approach.
Each country has a set of rules;
UK
1) Cut off first 3 characters from the string
2) Cut off all characters before and including /
3) Cut off all characters after and including ?
FR
1) Cut off first 4 characters from the string
2) Cut off all characters before and including @
3) Cut off all character after and including ?
So then I follow this procedure;
ID =
// Var UK
Var UK1 = Cut off first 3 characters from the string
Var UK2 = Cut off all characters before and including /
Var UK3 = Cut off all characters after and including ?
Var UK = Var UK 3
// Var FR
Var FR1 = IF(Page = Var UK THEN Var UK ELSE Cut off first 3 characters from the string)
Var FR2 = Cut off all characters before and including @
Var FR3 = Cut off all character after and including ?
Var FR = Var FR 3
// Var ID
Var ID = Var FR
Return
Var ID
Am I working in the most efficient way? Are there better solutions?
Solved! Go to Solution.
@Anonymous
Try this. I am not 100% sure.
Please could you paste data in copiable format instead if image
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvXWL0tMTsxLrtS3NDQyMrPX003NLcjJr0xNVYrViVZyC3LUBwtkFqcUJ6YkGpkYx5QaGBin6jtYGIKAia6evlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Page = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Page", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Start([Page],2)="UK" then Text.BetweenDelimiters([Page],"/", "?",1) else if Text.Start([Page],3)="FRA" then Text.BetweenDelimiters([Page],"@", "-") else null) in #"Added Custom"
@Anonymous
Try this. I am not 100% sure.
Please could you paste data in copiable format instead if image
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvXWL0tMTsxLrtS3NDQyMrPX003NLcjJr0xNVYrViVZyC3LUBwtkFqcUJ6YkGpkYx5QaGBin6jtYGIKAia6evlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Page = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Page", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Start([Page],2)="UK" then Text.BetweenDelimiters([Page],"/", "?",1) else if Text.Start([Page],3)="FRA" then Text.BetweenDelimiters([Page],"@", "-") else null) in #"Added Custom"
@Zubair_Muhammad
Thanks very much. I will try tommorow morning.
Yes, I will paste the data in the right format tommorow morning.
Thanks again
@Zubair_Muhammad
It works ! 🙂
Thanks very much. I'm learning a lot about text extraction.
Now, I hope I can solve the full problem I have.
Hopefully you can help me with the last steps 🙂
Data
UK/vacancy/91226?.-employee<af44g>3.3u22u37666fff_ | 91226 |
UK/vacancy-employee</21226?.->3.3.u376641 fff | 21226 |
UK/vacancy-employee155-</44226?>3.3uu37ff54215f_ | 44226 |
FRA/emplois vacants.employé.243>/@811114-./3uu37ff263337f_? | 811114 |
FRA/emplois vacants /employé?<af4fg>aa/@833114-./uu37ff443211f_? | 833114 |
FRA/emplois vacants/employé?a3f44>3. /@133114-./ff4121_?155654 | 133114 |
UK/vacancy.11-employee<af4fg>3.3u76117u37fff_/79856? | 79856 |
UK/vacancy-employee<44>3F/91156?.3.u344447f87ff_ | 91156 |
FRA/emplois vacants /employé?<af4fg>aa/@133114-./uu37f6442251ff_? | 133114 |
FRA/emplois vacants /employé?a568>3./@187114-./711112 ff_? | 187114 |
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |