Forum Discussion
Power BI Data validity
Hello All, I am building a data quality dashboard. One column is of PAN number, to check its validity, I need to apply 3 rules and finally calculate validity % based on that.
1) First 5 uppercase alphabets followed by 4 numeric digits and ending with one uppercase alphabet
2) The fourth character should be from list ("P","C", "H", "F", "A", "T", "B", "L", "J", "G")
3) Same PAN can't be present across multiple clients/rows
Please help. How can I achieve all these 3 parameters? In DAX or Power Query editor?
Hi Anonymous ,
Please check:
1. Add an Index column.
2. Add a custom column.
=if Text.Middle([PAN],0,5) <> Text.Upper(Text.Middle([PAN],0,5)) then "Invalid" else if try Number.IsNaN(Number.FromText(Text.Middle([PAN],5,4)) )otherwise 0 = 0 then "Invalid" else if Text.End([PAN],1)<> Text.Upper(Text.End([PAN],1)) then "Invalid" else if let Pan_ = [PAN],Index_= [Index] in Table.RowCount(Table.SelectRows(#"Added Index",each [PAN]=Pan_ and [Index]<=Index_))<>1 then "Invalid" else if List.Contains( {"P", "C", "H", "F", "A", "T", "B", "L", "J", "G"},Text.Middle([PAN],3,1)) then "Valid" else "Invalid"3. Change [Validity] type from "Any" to "Text".
4. Remove [Index] column.
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous ,
https://docs.microsoft.com/en-us/powerquery-m/text-functions
You need to use text.length, text.start
For Number
https://docs.microsoft.com/en-us/powerquery-m/number-isnan
Text.middle([Pan Number],4,1) in {"P","C", "H", "F", "A", "T", "B", "L", "J", "G"}
Hi Anonymous ,
Please check:
1. Add an Index column.
2. Add a custom column.
=if Text.Middle([PAN],0,5) <> Text.Upper(Text.Middle([PAN],0,5)) then "Invalid" else if try Number.IsNaN(Number.FromText(Text.Middle([PAN],5,4)) )otherwise 0 = 0 then "Invalid" else if Text.End([PAN],1)<> Text.Upper(Text.End([PAN],1)) then "Invalid" else if let Pan_ = [PAN],Index_= [Index] in Table.RowCount(Table.SelectRows(#"Added Index",each [PAN]=Pan_ and [Index]<=Index_))<>1 then "Invalid" else if List.Contains( {"P", "C", "H", "F", "A", "T", "B", "L", "J", "G"},Text.Middle([PAN],3,1)) then "Valid" else "Invalid"3. Change [Validity] type from "Any" to "Text".
4. Remove [Index] column.
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
