Forum Discussion
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 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.
5 Replies
hi Anonymous ,
please provide a sample input table and output table/ screenshot.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.
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.
Thanks datafi3nd
Can you tell how to build such query on my example? Sounds optimistic and straightforward this is what I am looking for.
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
