Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
francoisl
Helper I
Helper I

splitting url data

HI all, 

 

I have seen a lot of answers for similar issue but nothing like I am expecting. 

I have a URL and I need to decipher data from that url. 

 

Here is the search part of a sample of the url. 

 

searchType=Wheels&years=1985&makes=1985_Chevrolet&model=1985_Chevrolet_Camaro&submodel=1985_Chevrolet_Camaro_Berlinetta

 

I want to create one column for the Search Type, one columns for years..... 

 

Is there a way to split this easily like a function that you would pass the url and the argument that would return the value??

ex : geturlvalue (url, 'SearchType') returning 'Wheels'

 

Just a thought.  either in M or Dax. 

 

F

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @francoisl ,
You can try using following custom function code on 'query edit' side to look up specific text:

let
    LookupValue=(sample as text,search as text) =>
    let
        Source= List.Transform(Text.Split(sample,"&"), each [Key = Text.Split(_,"="){0}, Value = Text.Split(_,"="){1}]),
        Result= List.Transform(List.Select(Source, each Record.Field(_,"Key")=search), each Record.Field(_,"Value")){0}
    in
        Result
in
    LookupValue

Notice: If your result contains multiple results, please remove the bold part {0} to get full search result list.
Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @francoisl ,
You can try using following custom function code on 'query edit' side to look up specific text:

let
    LookupValue=(sample as text,search as text) =>
    let
        Source= List.Transform(Text.Split(sample,"&"), each [Key = Text.Split(_,"="){0}, Value = Text.Split(_,"="){1}]),
        Result= List.Transform(List.Select(Source, each Record.Field(_,"Key")=search), each Record.Field(_,"Value")){0}
    in
        Result
in
    LookupValue

Notice: If your result contains multiple results, please remove the bold part {0} to get full search result list.
Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi, 

 

Excellent solution, I think you need a different mindset to chop down text like this. 

In fact, both solutions were good but M is better than DAX since I merged data  from different sources. 

 

Many thanks 

 

Francois

mauriciosotero
Resolver III
Resolver III

Hi,

 

Check if this solution for a calculated column (Model example) will help you:

 

Model = 
Var ParameterFind = "model" & "="
Var StartPosition = FIND(ParameterFind,'Table'[URL]) + +LEN(ParameterFind)
Var NumberCharacteres = FIND("&",'Table'[URL],StartPosition,LEN('Table'[URL])+1) - StartPosition

RETURN
MID('Table'[URL], StartPosition, NumberCharacteres)

 

If it helps, pls mark this post as a solution and give a kudo. Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.