cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoeCochran
Frequent Visitor

Use only a portion of scanned barcode for filtering

Hello! I work for a retail grocer, and we would like to use the barcode scanning function in Power BI to filter reports, but many of our items use "System 2" UPCs - where the first 5 (or 6) digits of the barcode identify the item, and the last 5 encode the price/weight information for scale label items. In our database, the UPC for these items is stored as the 5 digit item number, followed by 5 zeros - as the last 5 digits are unique to each physical item. 

 

My issue is that Power BI seems to be capable of only filtering when the barcode is an exact match to the number in the "Barcode" data column. I'm hoping the many smart minds on this forum might be able to help think of a solution to this problem - if there is one! The only thing I can think of would be some sort of function that allows filtering based on part of the scanned barcode - somthing like a LEFT() function for the scanned data prior to filtering.

 

Thanks for the time and help!

8 REPLIES 8
MOS_DIMOV
New Member

Hello together, I have exactly the same issue.


My barcode is printed from UPS and scanned by PowerBi Mobile Barcode Scanner is:
1100508267723010880032009009

And my product barcode is:
50826772301088

I need to tell the scanner to skip the first 4 symbols:
1100

And read only the next 14 symbols of the printed barcode.
Have you already found a solution?

talya286
New Member

I have the exact same issue @JoeCochran 

 Let me give you guys an example

 

Let's pretend you just bought some ground beef

Your barcode looks like this-

 

IMG_9256.jpg

the LAST 5 digits (14055) in the barcode is the actual price of the item ($14.05)

these 5 digits obviously change all the time (because the weight is different for each package of ground beef)

 

BUT

the FIRST 7 digits (2055809) are consistently the same and never change for this item

 

So you might buy 1 beef item with the UPC 205580914055 - which costs $14.05

And a 2nd beef items with the UPC 205580918295 - which costs $18.29

 

 In the database - both of these items are link up to just 1 line item - for item code 205580900000

 

How can we make the scanner understand that when we scan 205580914055 we want it to retrieve data associated with code 205580900000?

AlB
Super User
Super User

@JoeCochran 

I'm assuming that you have the read barcodes in a column named [NumFromScanner] (or whatever other name) in a table in power query. Then you can create a custom column with the coded provided earlier. And with that custom column do the matching with your dataset table

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

 

AlB
Super User
Super User

@JoeCochran 

It's not completely clear to me. It would help if you show an example of your tables, what they look like now and what the ideal result would be, explaining the rationale for the steps. 

You could do something like:

Text.Start ( [NumFromScanner], 5) & "00000"

With this you would be changing the last 5 digits to zero while keeping the first 5 unchanged. The result would then be in the same format as the numbers in your dataset and you can look for an exact match.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

 

JoeCochran
Frequent Visitor

Hi - thanks for responding! I'm unsure how to use the code you provided - how do you use [NumFromScanner]? It is not a field in any of my data sets - this is the number that is coming directly from the barcode scanner function in PowerBI. Are you saying that the value that is scanned is usable in functions? How would I go about doing that? Thanks!

JoeCochran
Frequent Visitor

Hi All,

 

  Thanks for taking a minute to respond - but I may not have made the initial issue clear. I can't see how using Left or Split here on the dataset would make any difference. My understanding is that using the PBI Barcode scanner requires an exact match within the dataset. What I need to be able to do is perform a Left or Split on the scanned barcode BEFORE it matches with anything in the dataset. For example:

 

Number in my dataset: 21234500000

Number scanned by barcode scanner: 21234500499

 

The bottom number that was scanned by the barcode scanner has the same first 6 digits - but after that, it could be ANY 5 digits. In this type of barcode, the last 5 digits (before the checksum) are used to encode the price on scale label items. In the above example, the price would be $4.99. In our data tables, we store these types of codes with the last 5 digits being "0". We carry thousands of these items, so making another table - even generated - in PBI would conatin billions of rows to get all available matches (99,999 possible matches for each item, each requiring a seperate row to match).

 

I'm hoping someone might have a more clever way of solving this particular conundrum. Thanks!

v-stephen-msft
Community Support
Community Support

Hi @JoeCochran ,

 

Yes, using LEFT is a good choice. 

 

In addition, you may use Split Column in the power query editor.

6.png

 

Reference: https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

 

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AlB
Super User
Super User

Hi @JoeCochran 

Can you provide some sample data and an example based on that data? With the expected result?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors