Forum Discussion
How can I exclude certain results based on format?
I have a table and I want to exclude any result that has an incorrectly formatted serial number e.g. less or more than 9 digits
The serial numbers are alpha-numeric, and it doesn't matter what they are made up of, as long as there is only 9 digits in each one.
Thank you
Hi TiaVig ,
You could create a measure ,then use the measure to filter the table:
test = IF(LEN(MAX('Table'[Column1]))<9,0,1)And you also could create another table ,base on the first table:
Table 2 = CALCULATETABLE('Table',FILTER('Table',LEN('Table'[Column1])>=9))If use power query ,test the below to custom column:
= Table.AddColumn(Source, "len", each if Text.Length([Column1])>=9 then 1 else 0)Then filter:
Did I answer your question? Mark my post as a solution!
Best RegardsLucien
2 Replies
- AlexanderPrime
Solution Supplier
Quickest way is to make a new column that counts how many characters are in that column. Then filter your result to only show results with the correct number of characters declared in that column.
In Power Query, add a custom column and use the following:
Text.Length([#"Serial No."])
Then filter the table to show only results in that column returning the number 9.
- v-luwang-msft
Community Support
Hi TiaVig ,
You could create a measure ,then use the measure to filter the table:
test = IF(LEN(MAX('Table'[Column1]))<9,0,1)And you also could create another table ,base on the first table:
Table 2 = CALCULATETABLE('Table',FILTER('Table',LEN('Table'[Column1])>=9))If use power query ,test the below to custom column:
= Table.AddColumn(Source, "len", each if Text.Length([Column1])>=9 then 1 else 0)Then filter:
Did I answer your question? Mark my post as a solution!
Best RegardsLucien
