Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have the following code but for some reason it is returning 54225690 as Invalid - can anyone see why - even chatgpt is saying everything looks OK?
Thanks
Landline Quality =
IF(
'Contact Details (Email & Phone)'[LandLine Number] = "",
"Missing",
IF(
LEN('Contact Details (Email & Phone)'[LandLine Number]) < 5
|| LEN('Contact Details (Email & Phone)'[LandLine Number]) > 15,
"Invalid",
SWITCH(
TRUE(),
IFERROR(FIND("*", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND("&", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND("%", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND("$", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND("£", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND("!", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND("^", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND("+", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND(";", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND(":", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND(" ", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND("?", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND("<", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND("-", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND(" ", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND("(", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND(")", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND("[", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
IFERROR(FIND("]", 'Contact Details (Email & Phone)'[LandLine Number]), 0) > 0, "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*1111*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*2222*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*3333*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*4444*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*5555*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*6666*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*7777*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "0*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*8888*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*9999*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*00000*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*012345*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*123456*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*234567*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*345678*"), "Invalid",
CONTAINSSTRING('Contact Details (Email & Phone)'[LandLine Number], "*456789*"), "Invalid",
"Valid"
)
)
)
For some reason, making it a custom column in Power Query fixed this issue
each let
LandLine = [Landline Number],
lenLandLine = Text.Length(LandLine),
containsInvalidChar = List.AnyTrue(List.Transform({"*", "&", "%", "$", "£", "!", "^", "+", ";", ":", " ", "?", "<", "-", "(", ")", "[", "]"}, each Text.Contains(LandLine, _))),
containsInvalidSequence = List.AnyTrue(List.Transform({"*1111*", "*2222*", "*3333*", "*4444*", "*5555*", "*6666*", "*7777*", "0*", "*8888*", "*9999*", "*00000*", "*012345*", "*123456*", "*234567*", "*345678*", "*456789*"}, each Text.Contains(LandLine, _)))
in
if LandLine = "" then "Missing"
else if lenLandLine < 5 or lenLandLine > 15 or containsInvalidChar or containsInvalidSequence then "Invalid"
else "Valid")
It looks as if your compiler has interpreted some of your code as a comment (the green text about halfway down):
I wonder if it has thrown something else out of kilter.
I think thats more an issue with the code inputter on the forum
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
61 |
User | Count |
---|---|
199 | |
120 | |
110 | |
79 | |
69 |