Home › Forums › Power Pivot › Excel UPC decoder into M
Tagged: M
This topic contains 1 reply, has 2 voices, and was last updated by tomallan 3 months, 4 weeks ago.

AuthorPosts

September 26, 2018 at 1:38 am #10670
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(10MOD((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.September 26, 2018 at 6:39 am #10673Keep 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 ) )

AuthorPosts
You must be logged in to reply to this topic.