Count all fields with pattern

[Anonymous]

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 of them have a hyperlinks which is good and they should be visible in their applicable rows. The rest five have either blank or have some unexpected field like minus etc. - those field I would like to count (maybe in the seperate column? I am not sure here, 5 correct, 5 not) and the best to highlite with some color, let's say red to show quickly which fields are corecctly filled out and which not.

[datafi3nd]

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]

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]

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

[adudani]

hi @Anonymous ,

please provide a sample input table and output table/ screenshot.

[Anonymous]

Sure. Exemplary table:

ID |  Manufacturer  |  Hyperlink             |

1  |  Tesla                | http://tesla.co        |

2  |  Nissan             | http://nissan.com  | 

3  |  Renault           |                                |

4  |  Volvo              |         -                      |

5  | Toyota             |  N/A                        |

1. I would like to have counted all "blanks" , "-" and "N/A" (maybe also others sign) catched and counted. But I do not know what user might put in "hyperlink" field except of above examples that's why better to have some kind of regex which counted all wrongly filled out fields. I mean for example those not starting from http:// and maybe show me with some tooltip? visualisation? another column? Not sure here. 

2. Also, all those field without http:// should be marked in red to see at once.