POWER QUERY UPCA CHECK DIGIT

[lwarren1968]

Can someone review the CheckDigit Calculation portion below? Something is off. Example 02636267000 returned check digit of 6 however it is actually 2

let
Source = Excel.Workbook(File.Contents("J:\PRICE\QUERIES\144F\144F.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Replaced Value" = Table.ReplaceValue(#"Promoted Headers", null, 0, Replacer.ReplaceValue, {"DEALER UNCOATED"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Replaced Value", {{"Column9", type text}, {"DEALER UNCOATED", type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type5", null, 0, Replacer.ReplaceValue, {"MSRP PRICE UNCOATED"}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Replaced Value1", {{"DEALER UNCOATED", type text}}, "en-US"), "DEALER UNCOATED", Splitter.SplitTextByDelimiter(" ", QuoteStyle.None), {"DEALER UNCOATED.1", "DEALER UNCOATED.2", "DEALER UNCOATED.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter", {{"DEALER UNCOATED.1", type text}, {"DEALER UNCOATED.2", type number}, {"DEALER UNCOATED.3", type text}}),
#"Replaced Value2" = Table.ReplaceValue(#"Changed Type", "$", "", Replacer.ReplaceText, {"DEALER UNCOATED.1"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value2", {{"DEALER UNCOATED.2", type text}}, "en-US"), {"DEALER UNCOATED.1", "DEALER UNCOATED.2"}, Combiner.CombineTextByDelimiter("", QuoteStyle.None), "Merged"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns", {{"Merged", type number}, {"DEALER UNCOATED.3", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1", {"DEALER UNCOATED.3"}),
#"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Columns", {{"MSRP PRICE UNCOATED", type text}}, "en-US"), "MSRP PRICE UNCOATED", Splitter.SplitTextByDelimiter(" ", QuoteStyle.None), {"MSRP PRICE UNCOATED.1", "MSRP PRICE UNCOATED.2", "MSRP PRICE UNCOATED.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1", {{"MSRP PRICE UNCOATED.1", type text}, {"MSRP PRICE UNCOATED.2", type number}, {"MSRP PRICE UNCOATED.3", type text}}),
#"Replaced Value3" = Table.ReplaceValue(#"Changed Type2", "$", "", Replacer.ReplaceText, {"MSRP PRICE UNCOATED.1"}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value3", {{"MSRP PRICE UNCOATED.2", type text}}, "en-US"), {"MSRP PRICE UNCOATED.1", "MSRP PRICE UNCOATED.2"}, Combiner.CombineTextByDelimiter("", QuoteStyle.None), "Merged.1"),
#"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns1", {{"Merged.1", type number}, {"MSRP PRICE UNCOATED.3", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type3", {"MSRP PRICE UNCOATED.3"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns1", {{"Merged", Currency.Type}, {"Merged.1", Currency.Type}}),
#"Replaced Value4" = Table.ReplaceValue(#"Changed Type4", "-", "", Replacer.ReplaceText, {"UPC"}),
#"Changed Type6" = Table.TransformColumnTypes(#"Replaced Value4", {{"UPC", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type6", {{"UPC", "UPC11"}}),

// CheckDigit Calculation
AddCheckDigit = Table.AddColumn(#"Renamed Columns", "CheckDigit", each
let
UPC11Text = Text.From([UPC11]),
DigitsList = Text.ToList(UPC11Text),
OddSum = List.Sum(List.Transform(List.Alternate(DigitsList, 1, 1), each Number.FromText(_))),
EvenSum = List.Sum(List.Transform(List.Alternate(DigitsList, 0, 1), each Number.FromText(_))),
TotalSum = OddSum * 3 + EvenSum,
Mod10 = Number.Mod(TotalSum, 10),
CheckDigit = if Mod10 = 0 then 0 else 10 - Mod10
in
CheckDigit
)
in
AddCheckDigit

 

[Uncle Gizmo]

I note that your question has yet to receive a reply. More than likely because there are low numbers of people attending the forum due to the holidays. I'm posting this message to bump your message up the list so that it hopefully gets a second look. Best of luck and happy new year!

 

[Gasman]

I ignored it due to no code tags and a pain to read.
Also I do not recognise the language.

 

[NauticalGent]

Looks like PowerQuery...I've used it once but since I can achieve the same thing with text import options, I left it alone.

Edit: Since the word "Power Query" is in the title, I guess that makes me eligible for the "No $h!t, Sherlock" award...?

 

[NauticalGent]

From ChatGPT:

In this specific code snippet, it looks like a custom column is being added to a table ("#Renamed Columns"). The new column is named "CheckDigit," and its values are calculated based on the UPC11 column in the table. The calculation involves transforming the UPC11 values into a list of digits, summing the odd and even positions separately, performing some arithmetic operations, and finally determining the check digit. The result is a new table with an additional "CheckDigit" column.

Sound about right?

 

[Gasman]

Looks like PowerQuery...I've used it once but since I can achieve the same thing with text import options, I left it alone.

Edit: Since the word "Power Query" is in the title, I guess that makes me eligible for the "No $h!t, Sherlock" award...?

Yes John, but not one I recognise.
Nor able to use, as I am on 2007?
I do know that there are several methods of calculating a check digit. The banks here in the UK, use different algorithims, so you need to know which bank the data came from, so as to apply the correct version.

 

[NauticalGent]

Gotta love ChatGPT...

I asked Chatty what would a value of "02636267000" produce? Her answer:

UPC11Text = "02636267000"
DigitsList = {0, 2, 6, 3, 6, 2, 6, 7, 0, 0, 0}
OddSum = Sum of odd-positioned digits = 0 + 6 + 6 + 6 + 6 + 0 = 24
EvenSum = Sum of even-positioned digits = 2 + 3 + 2 + 7 + 0 = 14
TotalSum = OddSum * 3 + EvenSum = 24 * 3 + 14 = 86
Mod10 = TotalSum Mod 10 = 86 Mod 10 = 6
CheckDigit = If Mod10 = 0 then 0 else 10 - Mod10 = 10 - 6 = 4

 

[Gasman]

 

[Gasman]

Gotta love ChatGPT...

I asked Chatty what would a value of "02636267000" produce? Her answer:

The trouble is, people will just take that as it is and not check it?

 

[NauticalGent]

Yep, that is an issue. I have been using it to convert my VB Scripts to Power Shell. She gets it wrong a LOT at first but is able to build on the conversation and we eventually get there...