none
Conversion of Excel Formula to Custom Column Formula RRS feed

  • Question

  • I  have a data file containing UPCs in column C, formatted as 00-00000-00000.  Note that this does not contain the check digit.  

    I have the following formula for a neighboring column that will add the check digit and re-format the UPC as 000000000000.  (This formula is for row 14.):

    =IF(LEN(IF(LEN(VALUE(LEFT(C14,2)&MID(C14,4,5)&RIGHT(C14,5)))<12,TEXT(D14,"00000000000"),IF(LEN(VALUE
    (LEFT(C14,2)&MID(C14,4,5)&RIGHT(C14,5)))=12,TEXT(VALUE(LEFT(C14,2)&MID(C14,4,5)&RIGHT
    (C14,5)),"000000000000"),"UPC CHAR ERR")))=11,TEXT(IF(LEN(VALUE(LEFT(C14,2)&MID(C14,4,5)&RIGHT(C14,5)))
    <12,TEXT(D14,"00000000000"),IF(LEN(VALUE(LEFT(C14,2)&MID(C14,4,5)&RIGHT(C14,5)))=12,TEXT(VALUE(LEFT(C14,2)
    &MID(C14,4,5)&RIGHT(C14,5)),"000000000000"),"UPC CHAR ERR"))&RIGHT(IF(LEN(E14)=11,(10-MOD(((MID
    (E14,1,1)+MID(E14,3,1)+MID(E14,5,1)+MID(E14,7,1)+MID(E14,9,1)+MID(E14,11,1))*3+MID(E14,2,1)+MID
    (E14,4,1)+MID(E14,6,1)+MID(E14,8,1)+MID(E14,10,1)),10)),IF(LEN(E14)=12,(10-MOD(((MID(E14,2,1)+MID
    (E14,4,1)+MID(E14,6,1)+MID(E14,8,1)+MID(E14,10,1)+MID(E14,12,1))*3+MID(E14,1,1)+MID(E14,3,1)+MID
    (E14,5,1)+MID(E14,7,1)+MID(E14,9,1)+MID(E14,11,1)),10)),"UPC CHAR ERR")),1),"000000000000"),IF(LEN(IF(LEN
    (VALUE(LEFT(C14,2)&MID(C14,4,5)&RIGHT(C14,5)))<12,TEXT(D14,"00000000000"),IF(LEN(VALUE(LEFT(C14,2)&MID
    (C14,4,5)&RIGHT(C14,5)))=12,TEXT(VALUE(LEFT(C14,2)&MID(C14,4,5)&RIGHT(C14,5)),"000000000000"),"UPC CHAR 
    ERR")))=12,TEXT(IF(LEN(VALUE(LEFT(C14,2)&MID(C14,4,5)&RIGHT(C14,5)))<12,TEXT(D14,"00000000000"),IF(LEN
    (VALUE(LEFT(C14,2)&MID(C14,4,5)&RIGHT(C14,5)))=12,TEXT(VALUE(LEFT(C14,2)&MID(C14,4,5)&RIGHT
    (C14,5)),"000000000000"),"UPC CHAR ERR"))&RIGHT(IF(LEN(E14)=11,(10-MOD(((MID(E14,1,1)+MID(E14,3,1)+MID
    (E14,5,1)+MID(E14,7,1)+MID(E14,9,1)+MID(E14,11,1))*3+MID(E14,2,1)+MID(E14,4,1)+MID(E14,6,1)+MID
    (E14,8,1)+MID(E14,10,1)),10)),IF(LEN(E14)=12,(10-MOD(((MID(E14,2,1)+MID(E14,4,1)+MID(E14,6,1)+MID
    (E14,8,1)+MID(E14,10,1)+MID(E14,12,1))*3+MID(E14,1,1)+MID(E14,3,1)+MID(E14,5,1)+MID(E14,7,1)+MID
    (E14,9,1)+MID(E14,11,1)),10)),"UPC CHAR ERR")),1),"0000000000000"),"UPC CHAR ERR"))

    This formula works fine in Excel, but I'd like to be able to pull the original data into a Power Query, add a custom column, and have this custom column contain the re-formatted data.  This would save me much time down the road.

    How can I go about converting this Excel formula to a formula that will work in Power Query's custom column definition?

    Thanks!

    Tuesday, November 17, 2015 7:17 PM

Answers

  • After you import the data into Power Query, I'd suggest that you first remove the hyphens from the codes. Select the code column, and from the Query Editor Ribbon, choose Transform-->Replace. In the Replace Values dialog box that appears, enter the hyphen character (without quotes) in the Value To Find text box, and leave the Replace With box blank. Click OK.

    The following custom function generates GS1 check digits for GTIN-8, GTIN-12, GTIN-13, GTIN-14, and SSCC codes. I've named the function NumberGS1CheckDigit (however, you can name it whatever you want).

    (gs1Code as text) as any =>
    let 
     ValidCodeLengths = {7,11,12,13,17},
     CodeLength = Text.Length(gs1Code),
     CodeToNumber = try Number.FromText(gs1Code),
     IsCodeLengthCorrect = try if List.Contains(ValidCodeLengths,CodeLength) then true
            else error "Incorrect GS1 code length. Ensure that check digit is not
             included in code.",
     CodeList = Text.ToList(gs1Code),
     GS1Multiplier = List.LastN({3,1,3,1,3,1,3,1,3,1,3,1,3,1,3,1,3},CodeLength),
     AccumulatedTotal = List.Accumulate(List.Positions(CodeList),0,(accumulated,current) =>
          accumulated + Number.FromText(CodeList{current}) * GS1Multiplier{current}), 
     CheckDigit =  if CodeToNumber[HasError] then "GS1 code contains non-numeric characters."
        else if IsCodeLengthCorrect[HasError] then IsCodeLengthCorrect[Error][Message]
        else if 10 - Number.Mod(AccumulatedTotal,10) = 10 then 0
        else 10 - Number.Mod(AccumulatedTotal,10)
    in
     CheckDigit

    In your particular scenario, which is equivalent to GTIN-13, you may want to calculate the check digit for the GTIN-13 code only, with anything else considered an error. In this case, In the ValidCodeLengths step, you simply remove all other values except 12. Alternatively, you could remove the ValidCodeLengths step, and in the IsCodeLengthCorrect step, change the predicate to if CodeLength = 12 then ...

    After creating the custom function (just paste the code into a blank query and name it), you create a custom column to use it, e.g. if the name of the code column is UPC, I'd suggest that the first custom column be a Check Digit column i.e. NumberGS1CheckDigit([UPC]) (or whatever name that you called the function). If there are errors in the Check Digit column (incorrect length or non-numeric characters in the UPC code), an error description will let you know what the problem is. The next custom column might be:

    [UPC] & Number.ToText([Check Digit]), which adds the check digit from the previously created Check Digit column to the end of the original UPC code. Note that errors in the Check Digit column will display "Error" in the new column. You can either remove the rows with errors, or fix the problem(s) in the UPC source.


    Wednesday, November 18, 2015 7:48 PM

All replies

  • Hello BKvistad

    Can you place a short sample of data and formulas to onedrive.com? I'll try to preform this in PQ, but my Excel local is not English and translate such long formula is very difficult


    Maxim Zelensky Excel Inside

    Wednesday, November 18, 2015 7:28 PM
  • After you import the data into Power Query, I'd suggest that you first remove the hyphens from the codes. Select the code column, and from the Query Editor Ribbon, choose Transform-->Replace. In the Replace Values dialog box that appears, enter the hyphen character (without quotes) in the Value To Find text box, and leave the Replace With box blank. Click OK.

    The following custom function generates GS1 check digits for GTIN-8, GTIN-12, GTIN-13, GTIN-14, and SSCC codes. I've named the function NumberGS1CheckDigit (however, you can name it whatever you want).

    (gs1Code as text) as any =>
    let 
     ValidCodeLengths = {7,11,12,13,17},
     CodeLength = Text.Length(gs1Code),
     CodeToNumber = try Number.FromText(gs1Code),
     IsCodeLengthCorrect = try if List.Contains(ValidCodeLengths,CodeLength) then true
            else error "Incorrect GS1 code length. Ensure that check digit is not
             included in code.",
     CodeList = Text.ToList(gs1Code),
     GS1Multiplier = List.LastN({3,1,3,1,3,1,3,1,3,1,3,1,3,1,3,1,3},CodeLength),
     AccumulatedTotal = List.Accumulate(List.Positions(CodeList),0,(accumulated,current) =>
          accumulated + Number.FromText(CodeList{current}) * GS1Multiplier{current}), 
     CheckDigit =  if CodeToNumber[HasError] then "GS1 code contains non-numeric characters."
        else if IsCodeLengthCorrect[HasError] then IsCodeLengthCorrect[Error][Message]
        else if 10 - Number.Mod(AccumulatedTotal,10) = 10 then 0
        else 10 - Number.Mod(AccumulatedTotal,10)
    in
     CheckDigit

    In your particular scenario, which is equivalent to GTIN-13, you may want to calculate the check digit for the GTIN-13 code only, with anything else considered an error. In this case, In the ValidCodeLengths step, you simply remove all other values except 12. Alternatively, you could remove the ValidCodeLengths step, and in the IsCodeLengthCorrect step, change the predicate to if CodeLength = 12 then ...

    After creating the custom function (just paste the code into a blank query and name it), you create a custom column to use it, e.g. if the name of the code column is UPC, I'd suggest that the first custom column be a Check Digit column i.e. NumberGS1CheckDigit([UPC]) (or whatever name that you called the function). If there are errors in the Check Digit column (incorrect length or non-numeric characters in the UPC code), an error description will let you know what the problem is. The next custom column might be:

    [UPC] & Number.ToText([Check Digit]), which adds the check digit from the previously created Check Digit column to the end of the original UPC code. Note that errors in the Check Digit column will display "Error" in the new column. You can either remove the rows with errors, or fix the problem(s) in the UPC source.


    Wednesday, November 18, 2015 7:48 PM