Forum Discussion
Power BI Custom Filter Creation with DAX or any other inputs?
Hello Community! 🙂
How can one set a certain criteria which returns all the wrong format, i.e. which does not match the format like DE followed by 9 digits in Power BI columns? Basically whatever is not matching the defined format, should be displayed. Thank you in advance:)
Bit rushed earlier, here's an example
(_text as text) as logical =>
let_text_asList = Text.ToList(_text),
_length = 8,
_invalid_chars = Text.ToList("{}[]/\,. ;:'"),
_letters = {"A".."Z"},
fnCheckRange = (_string, _start, _count) as logical =>let
result = List.ContainsAll(
_letters,
Text.ToList(Text.Middle(_string,_start,_count))
)in
result,
//__tests__
_not_uppercase = _text <> Text.Upper(_text),
_wrong_length = Text.Length(_text) <> _length,
_contains_invalid_chars = List.ContainsAny(_text_asList,_invalid_chars),
_not_validChars = not fnCheckRange(_text,0,2),
_test_outcome = List.AnyTrue({_not_uppercase,
_wrong_length,
_contains_invalid_chars,
_not_validChars
})in
_test_outcome
3 Replies
Hi there, this isn't a complete answer but to start you off, you could write an M-query function that tests various components of a string and returns a flag to indicate if it's wrong. You can't do pattern matching so would have to use a combination of text and list functions to test various components, such as
- text extraction
- text membership
- text transformation
- list functions once the text is split into a list
Hope this helps
Bit rushed earlier, here's an example
(_text as text) as logical =>
let_text_asList = Text.ToList(_text),
_length = 8,
_invalid_chars = Text.ToList("{}[]/\,. ;:'"),
_letters = {"A".."Z"},
fnCheckRange = (_string, _start, _count) as logical =>let
result = List.ContainsAll(
_letters,
Text.ToList(Text.Middle(_string,_start,_count))
)in
result,
//__tests__
_not_uppercase = _text <> Text.Upper(_text),
_wrong_length = Text.Length(_text) <> _length,
_contains_invalid_chars = List.ContainsAny(_text_asList,_invalid_chars),
_not_validChars = not fnCheckRange(_text,0,2),
_test_outcome = List.AnyTrue({_not_uppercase,
_wrong_length,
_contains_invalid_chars,
_not_validChars
})in
_test_outcome
