Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:)
Solved! Go to Solution.
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
@Alex_T Thank you so much. I was trying to solve the same, I think this should work..
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
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