ID Validation using MQuery

[ysf]

Hi

I have a field that contains ID numbers that I would like to do validations as in the below explanation

For this explanation I am going to use ID number 860506 5 397 08 3

a) Add all the digits of the ID number in the odd positions (except for the last number, which is the control digit):

8+0+0+5+9+0 = 22

b) Take all the even digits as one number and multiply that by 2:

656378 * 2 = 1312756

c) Add the digits of this number together (in b)

1+3+1+2+7+5+6 = 25

d) Add the answer of C to the answer of A

22+25 = 47

e) Subtract the second character from D from 10, this number should now equal the control character (Last Digit on ID)

10-7 = 3 = control character (3)

I would like to do this within PowerQuery or even Dax

[lbendlin · Accepted solution]

@ysf 

You have not indicated in your sample data if you need this for an individual value or for a list of values. Here is a rough implementation for a single value.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAzMDUwUzBVMLY0VzCwUDBWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type", "Reverse ID", each Text.Reverse([ID])),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom4"," ","",Replacer.ReplaceText,{"Reverse ID"}),
    #"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Reverse ID", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Reverse ID"),
    #"Added Index" = Table.AddIndexColumn(#"Split Column by Position", "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Odds", each if [Index]=1 then 0 else if Number.IsOdd([Index]) then [Reverse ID] else 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Evens", each if Number.IsOdd([Index]) then 0 else [Reverse ID]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Odds", Int64.Type}, {"Evens", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"ID"}, {{"SumOdds", each List.Sum([Odds]), type number}, {"CatEvens",  each Text.Reverse(Text.Combine(#"Changed Type1"[Evens],""))}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Grouped Rows","0","",Replacer.ReplaceText,{"CatEvens"}),
    #"Added Custom3" = Table.AddColumn(#"Replaced Value1", "ProdEvens", each 2*Number.From([CatEvens])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"ProdEvens", type text}}),
    #"Split Column by Position1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type2", {{"ProdEvens", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ProdEvens"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position1",{{"ProdEvens", Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type3", {"ID", "SumOdds"}, {{"SumEvens", each List.Sum([ProdEvens]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Check", each 10- Number.Mod([SumOdds]+[SumEvens],10))
in
    #"Added Custom"

How to use this code: Create a new Blank Query, then click on "Advanced Editor", and then replace the code in the window with the code provided here. Then click "Done".

[lbendlin · Accepted solution]

@ysf 

You have not indicated in your sample data if you need this for an individual value or for a list of values. Here is a rough implementation for a single value.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAzMDUwUzBVMLY0VzCwUDBWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type", "Reverse ID", each Text.Reverse([ID])),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom4"," ","",Replacer.ReplaceText,{"Reverse ID"}),
    #"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Reverse ID", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Reverse ID"),
    #"Added Index" = Table.AddIndexColumn(#"Split Column by Position", "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Odds", each if [Index]=1 then 0 else if Number.IsOdd([Index]) then [Reverse ID] else 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Evens", each if Number.IsOdd([Index]) then 0 else [Reverse ID]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Odds", Int64.Type}, {"Evens", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"ID"}, {{"SumOdds", each List.Sum([Odds]), type number}, {"CatEvens",  each Text.Reverse(Text.Combine(#"Changed Type1"[Evens],""))}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Grouped Rows","0","",Replacer.ReplaceText,{"CatEvens"}),
    #"Added Custom3" = Table.AddColumn(#"Replaced Value1", "ProdEvens", each 2*Number.From([CatEvens])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"ProdEvens", type text}}),
    #"Split Column by Position1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type2", {{"ProdEvens", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ProdEvens"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position1",{{"ProdEvens", Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type3", {"ID", "SumOdds"}, {{"SumEvens", each List.Sum([ProdEvens]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Check", each 10- Number.Mod([SumOdds]+[SumEvens],10))
in
    #"Added Custom"

How to use this code: Create a new Blank Query, then click on "Advanced Editor", and then replace the code in the window with the code provided here. Then click "Done".

[ysf]

HI @lbendlin 

Thanks, this gives me an idea.

The field is within a dataset containing many records +-500 000

I will try and change it to suit my situation and come back to you.

Regards

[v-kelly-msft · Accepted solution]

Hi  @ysf ,

My M codes are slightly different from what suggested by @lbendlin ,you could also check it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAzMDUwUzBVMLY0VzCwUDBWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Column1]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Remove([Custom]," ")),
    #"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom1", {{"Custom.1", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom.1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Custom.1", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Column1"}, {{"Max", each List.Max([Index]), type number}, {"All", each _, type table [Column1=nullable text, Custom=text, Custom.1=nullable number, Index=number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Custom.1", "Index"}, {"All.Custom.1", "All.Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded All",{"Column1", "All.Custom.1", "All.Index", "Max"}),
    #"Added Custom2" = Table.AddColumn(#"Reordered Columns", "odd", each if [All.Index]=[Max] then null else  if Number.IsOdd([All.Index]) then [All.Custom.1] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "evens", each if [All.Index]=[Max] then null else  if Number.IsOdd([All.Index]) then null else [All.Custom.1]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"evens", type text}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type2", {"Column1"}, {{"Sum odds", each List.Sum([odd]), type nullable number}, {"CombineEvens", each Text.Combine(#"Changed Type2"[evens],"")}}),
    #"Added Custom4" = Table.AddColumn(#"Grouped Rows1", "Custom", each Number.FromText([CombineEvens])*2),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom4",{{"Custom", type text}}),
    #"Split Column by Position1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type3", {{"Custom", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Custom", Int64.Type}}),
    #"Grouped Rows2" = Table.Group(#"Changed Type4", {"Column1"}, {{"sum odds", each List.Max([Sum odds]), type nullable number}, {"c", each List.Sum([Custom]), type nullable number}}),
    #"Added Custom5" = Table.AddColumn(#"Grouped Rows2", "d", each [sum odds]+[c]),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Custom", each 10- Number.Mod([sum odds]+[c],10))
in
    #"Added Custom6"

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!