Home Forums Power Pivot Excel UPC decoder into M

Tagged: 

This topic contains 1 reply, has 2 voices, and was last updated by  tomallan 5 months, 3 weeks ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #10670

    IH8DATA
    Participant
    • Started: 3
    • Replies: 1
    • Total: 4

    Good evening,

    I am creating a lookup table that will tie two data sets together by UPC (Universal product code).  My lookup table only provides 12 digits for the UPC which needs to be decoded to show its check digit (the last digit needed).  I have the fomula in excel but I am having trouble getting it into query.  I can simply use a calculated column after my query but that is not clean and requires me to load the data to a table instead of just using a connection.

    The main problem comes when a UPC starts with a 0.  Here is the excel formula

    =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)

    I tried replacing MOD with Number.Mod and MID with Text.Middle.  The error I got said that we cannot apply “+” operators to text.  When I change the type to number on [UPC] it removes my leading 0.  I was hoping to find some kind of “Format” function that would change my [UPC] column to 12 charaters, all of which were numbers

    Can someone help with the formula above or help me find a way to turn UPC into a number while keeping the leading 0s?

     

    Attachments:
    You must be logged in to view attached files.
    #10673

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    Keep your UPC as text.

    The concatenation operator in Power Query is the &, not the +.

    Also, the first letter read by Text.Middle is 0, not 1.

    For starters, consider the following:

    =Text.From ( Number.Mod (
     10
        - Number.Mod (
            Int64.From ( 
    	  Text.From ( 
    	    Int64.From (
    	      Text.Middle ( [UPC], 11, 1 ) &
                  Text.Middle ( [UPC], 9, 1 )  & 
                  Text.Middle ( [UPC], 7, 1 )  & 
                  Text.Middle ( [UPC], 5, 1 )  & 
                  Text.Middle ( [UPC], 3, 1 )  & 
                  Text.Middle ( [UPC], 1, 1 )
                ) * 3 
    	 ) & 
    	 Text.Middle ( [UPC], 10, 1 ) & 
    	 Text.Middle ( [UPC], 8, 1 )  & 
    	 Text.Middle ( [UPC], 6, 1 )  & 
    	 Text.Middle ( [UPC], 4, 1 )  & 
    	 Text.Middle ( [UPC], 2, 1 )  & 
    	 Text.Middle ( [UPC], 0, 1 ) 
    	),
          10
         ),
      10
    ) )
    
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.