Help with MOD and MID to Number.Mod and Text.Range

[IH8DATA]

Good afternoon,

I'm new to M and just started to scratch the surface in the advanced editor. The Excel formula below let's me extract a check digit from a UPC. I need this so I can have a unique lookup value for my products. I've have replicated it in M but it looks ugly.

=MOD(10-MOD((MID([UPC],12,1)+MID([UPC],10,1)+MID([UPC],8,1)+MID([UPC],6,1)+MID([UPC],4,1)+MID([UPC],2,1))*3+MID([UPC],11,1)+MID([UPC],9,1)+MID([UPC],7,1)+MID([UPC],5,1)+MID([UPC],3,1)+MID([UPC],1,1),10),10)

Here is the code I am using in M. It is my first pass but it works. Anyone have an suggestions for making it cleaner?


Number.Mod ( 10 -
Number.Mod (
( Number.FromText ( Text.Range( [UPC 12 digit] , 12 - 1 , List.Min( {Text.Length ([UPC 12 digit] ) - (12 - 1) , 1} ) ) ) +
Number.FromText (Text.Range( [UPC 12 digit] , 10 - 1 , List.Min( {Text.Length ([UPC 12 digit] ) - (10 - 1) , 1} ) ) ) +
Number.FromText (Text.Range( [UPC 12 digit] , 8 - 1 , List.Min( {Text.Length ([UPC 12 digit] ) - (8 - 1) , 1} ) ) ) +
Number.FromText (Text.Range( [UPC 12 digit] , 6 - 1 , List.Min( {Text.Length ([UPC 12 digit] ) - (6 - 1) , 1} ) ) ) +
Number.FromText (Text.Range( [UPC 12 digit] , 4 - 1 , List.Min( {Text.Length ([UPC 12 digit] ) - (4 - 1) , 1} ) ) ) +
Number.FromText (Text.Range( [UPC 12 digit] , 2 - 1 , List.Min( {Text.Length ([UPC 12 digit] ) - (2 - 1) , 1} ) ) ) )* 3 +
Number.FromText (Text.Range( [UPC 12 digit] , 11 - 1 , List.Min( {Text.Length ([UPC 12 digit] ) - (11 - 1) , 1} ) ) ) +
Number.FromText (Text.Range( [UPC 12 digit] , 9 - 1 , List.Min( {Text.Length ([UPC 12 digit] ) - (9 - 1) , 1} ) ) ) +
Number.FromText (Text.Range( [UPC 12 digit] , 7 - 1 , List.Min( {Text.Length ([UPC 12 digit] ) - (7 - 1) , 1} ) ) ) +
Number.FromText (Text.Range( [UPC 12 digit] , 5 - 1 , List.Min( {Text.Length ([UPC 12 digit] ) - (5 - 1) , 1} ) ) ) +
Number.FromText (Text.Range( [UPC 12 digit] , 3 - 1 , List.Min( {Text.Length ([UPC 12 digit] ) - (3 - 1) , 1} ) ) ) +
Number.FromText (Text.Range( [UPC 12 digit] , 1 - 1 , List.Min( {Text.Length ([UPC 12 digit] ) - (1 - 1) , 1} ) ) ) , 10 ) , 10 )

 

[Ron Coderre]

This can probably be done more elegantly, but here's how I approached it (file attached).
Using a table of UPC codes named Table1 with a UPC column:

let
    Source                     = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SetDataTypes               = Table.TransformColumnTypes(Source,{{"UPC", type text}}),
    ConvertTextToList_Reverse  = Table.AddColumn(SetDataTypes, "Parsed", each List.Reverse(Text.ToList([UPC]))),
    ExpandLists                = Table.ExpandListColumn(ConvertTextToList_Reverse, "Parsed"),
    AddIndex                   = Table.AddIndexColumn(ExpandLists, "Index", 1, 1),
    AddAlternating1And0        = Table.AddColumn(AddIndex, "AltSeq", each Number.Mod([Index],2)),
    AddFactoredCol             = Table.AddColumn(AddAlternating1And0, "Factored", each 
                                 ([AltSeq]*2+1)*Number.From([Parsed])),
    GroupUPC_Sum_CalcDigit     = Table.Group(AddFactoredCol, {"UPC"}, {{"CheckDigit", each 
                                 Number.Mod(10-Number.Mod(List.Sum([Factored]),10),10), type number}})
in
    GroupUPC_Sum_CalcDigit

Is that something you can work with?

 

[IH8DATA]

This can probably be done more elegantly, but here's how I approached it (file attached).
Using a table of UPC codes named Table1 with a UPC column:

let
    Source                     = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SetDataTypes               = Table.TransformColumnTypes(Source,{{"UPC", type text}}),
    ConvertTextToList_Reverse  = Table.AddColumn(SetDataTypes, "Parsed", each List.Reverse(Text.ToList([UPC]))),
    ExpandLists                = Table.ExpandListColumn(ConvertTextToList_Reverse, "Parsed"),
    AddIndex                   = Table.AddIndexColumn(ExpandLists, "Index", 1, 1),
    AddAlternating1And0        = Table.AddColumn(AddIndex, "AltSeq", each Number.Mod([Index],2)),
    AddFactoredCol             = Table.AddColumn(AddAlternating1And0, "Factored", each 
                                 ([AltSeq]*2+1)*Number.From([Parsed])),
    GroupUPC_Sum_CalcDigit     = Table.Group(AddFactoredCol, {"UPC"}, {{"CheckDigit", each 
                                 Number.Mod(10-Number.Mod(List.Sum([Factored]),10),10), type number}})
in
    GroupUPC_Sum_CalcDigit

Is that something you can work with?

Hi Ron,

Thank you for this. I'm pretty new to M but was able to make most of this work regarless of figuring out how it works. I got jammed up on the last line, "GroupUPC_Sum_CalDigit". I'm unfamiliar with Table.Group and I got the ERROR > AddFactoredCol wasn't recognized.

Steve

 

[Ron Coderre]

Not sure what the issue is. I opened the file I posted and replaced the M-Code with the code I posted in the site and everything worked fine. Perhaps check your spelling or maybe post your code here so we can see what you're working with?

 

[p45cal]

I know this is almost 2 years old - I'm practising:

This can probably be done more elegantly

Perhaps with a function (called fnChkdgt):

(mynumber)=>
let
mynumbertxt=Number.ToText(mynumber),  
cstm=List.Transform(List.FirstN(Text.ToList(mynumbertxt),12),each Number.FromText(_)),
ChkNo = Number.Mod(10-Number.Mod(List.Sum(List.Alternate(cstm,1,1,0))*3+List.Sum(List.Alternate(cstm,1,1,1)), 10), 10)
in
ChkNo

used in the likes of:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UPC", Int64.Type}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Check Digit", each fnChkdgt([UPC])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Invoked Custom Function",{{"UPC", type text}})
in
    #"Changed Type1"

Also in the attached with a cross check to the original formula.