Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Count all fields with pattern

Hi,   I have a table where some of the fields do not match to my pattern and the pattern should be to take only those field which does not have a hyperlinks. Let's say that from 10 rows in column 5...
Need Help
datafi3nd's avatar
datafi3nd
Frequent Visitor
3 years ago

A likely idea (not sure if most efficient) would be add a column (with Power Query or DAX) to evaluate say 0 if hyperlink not valid and 1 for valid (if starts with http:// for example). You can then use the check to filter your table and as a rule for conditional formatting to do the colors.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks datafi3nd 

Can you tell how to build such query on my example? Sounds optimistic and straightforward this is what I am looking for.

  • adudani's avatar
    adudani
    Memorable Member
    3 years ago

    Nice idea datafi3nd ! have executed below

     

    Anonymous 

    create a blank query

    copy paste the code below into the advanced editor:

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRqDm0QME3Ma80LTG5pLQotQjIBQl5VBakFuVk5mUD2RioRilWJ1rJEKo0JLU4JxGbMgWIgoySkgIrff0SkDK95PxDC0AQJg02yQhqkl9mcXFiHlYb4cbkgdUAzckFGQMUB5tgDDUhKDUvsTSnBJt+7O7D6W6wsSZQY8Pyc8rycfkPma9LpMGmIJZCSH5lfgnWoAOHhr4jkS6NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1.4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID ", Int64.Type}, {"  Manufacturer  ", type text}, {"  Hyperlink             ", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.Contains([#"  Hyperlink             "], "http://") then 1 else 0)
in
    #"Added Custom"

     

     

     

    close and load this table.

     

    you could sum the Custom column using DAX or Use this column as a filter selecting 1 for manufacturers with a hypelink ( If the Hyperlink column contains https://) Manufacturer in rows to get valid ones

     

    Conditional Formatting could be applied instead if you don't want to filter 1 or 0

     

    See the solution: pattern.pbix