CODEWITHSUNDEEP
powerquery
Adam H
Adam H

Reputation: 1818

Simple function to loop over list of values and call Text.Replace

I'm trying to loop over a list of values and call Text.Replace for each item in the list. Essentially I'm trying to pull just the domain name out of a string.

For example if I have www.google.com I want the transform to result in google. Here is my attempt at it but it's giving me all sorts of errors.

let
    replacements = {"www.", ".ca", ".com" }, 
    replacement = "",
    doReplacements = (value) => {
        List.Transform(replacements, (find) => {
            value = Text.Replace(value, find, replacement)
        })
    },
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"domainname", type text}, 
        {"method", type text}, 
        {"Max bytes_received", Int64.Type}, 
        {"Max bytes_sent", Int64.Type}, 
        {"Max time_taken_ms", Int64.Type}
    }),
    replaced = Table.TransformColumns(#"Changed Type", {
        {"domainname", each let temp = [domainname] in Function.Invoke(doReplacements, {[temp]})}
    }),
    #"Grouped Rows" = Table.Group(replaced, {"domainname"}, {
        {"Verbs", each Text.Combine([method], ","), type text},
        {"bytes_received", each List.Max([Max bytes_received]), type number}, 
        {"bytes_sent", each List.Max([Max bytes_sent]), type number}, 
        {"time_taken", each List.Max([Max time_taken_ms]), type number}
    })
in
    #"Grouped Rows"

Upvotes: 0

Views: 469

Answers (2)

horseyride
horseyride

Reputation: 21318

Function to get to base URL. This handles a variety of examples a simpler version might not, including combinations of below

www.example.com
http://www.example.com
http://example.com
example.com
sub.example.com
example.com/directory/

Function CleanURL

(testurl as text) =>
let
Part1 = Text.Reverse(Text.BeforeDelimiter(Text.Reverse(testurl),"//")),
Part2 = Text.BeforeDelimiter(Part1,"/"),
Part3 = List.LastN(Text.Split(Part2,"."),2){0}
in Part3

Use

replaced = Table.TransformColumns(#"Changed Type", {{"domainname", each CleanURL(_), type text}})

or without the function as a confusing jumble

 replaced = Table.TransformColumns(#"Changed Type", {{"domainname", each List.LastN(Text.Split(Text.BeforeDelimiter(Text.Reverse(Text.BeforeDelimiter(Text.Reverse(_),"//")),"/"),"."),2){0}, type text}})

Upvotes: 1

Wedge
Wedge

Reputation: 1826

So, what you are trying to do is possible and I have implemented "replacement tables" in a function before... but you can also just extract text between delimiters and that is much simpler. Now, it's possible your actual list of data may include instances where this alone wouldn't work, but given the sample at least this would be adequate.

Table.TransformColumns(#"Changed Type", {{"domainname", each Text.BetweenDelimiters(_, ".", "."), type text}})

Upvotes: 1

Related Questions