Lookup parts of a SKU to check against inventory

[Anonymous]

Hello, 

So I have what I think is a fairly complex need, but maybe it's simpler than I think.  

I have a data model that contains a master item part number list.   Our sales person submit SKU requests that we'd like to look up against inventory to see if we have it in stock, or not.    The challenge is we don't inventory by SKU number.   We have to look at the SKU matrix to see, part by part, if we have the ability to build the SKU.    For example:   PB-TX5-Q-MW8   In this example, each of those 4 things is an item...each with its own inventory stock status.    If we had ALL of these in inventory, we could build the SKU.  

Definitely open to suggestions on how to approach this.  

[v-yuta-msft]

@Anonymous ,

Could you please share some sample data and clairfy more details about your requirement?

Regards,

Jimmy Tao

[Anonymous]

I can't share sample data, but I can provide more detail.  

For example, we have a SKU  TX-3-G-VXX-8.     Each one of these represents an item.   So, TX = a certain family type.   3 indicates a cable length.   G indicates a batterty type.   Etc...

An excel, you would probably have a table with a column that said Family Type.   The list of all the family types TX, GA, BC, etc.    Selecting TX, would then (maybe using vlookup) bring up all of the relative cables that went with that family type.   3 could indicate a 3 foot cable, but if the SKU was TX-6, that would then indicate it was part of the TX family, but a 6 foot cable was in this SKU.    

G might be a 270V battery.   But H could indicate 320V.    So, TX-6-H  =   Family is TX....cable is 6 foot....battery is 320V....etc....

To complicate things the nomenclature does not always follow the same length.  So you can't just do a simple colum split using a delimiter.     

What I need to do is to be able to have a SKU pull up....then match all of the independent parts of that SKU (as mentioned above) return with their available surplus.    So...a search for TX-6-H...would pull up the available inventory for TX...and the available inventory for 6.....and then H.....and if all were available  then manufacturing could know they had all of the components needed to build this SKU.    

[amitchandak]

Can you share sample data with the expected output? If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

I can't share sample data, but I can provide more detail.  

For example, we have a SKU  TX-3-G-VXX-8.     Each one of these represents an item.   So, TX = a certain family type.   3 indicates a cable length.   G indicates a batterty type.   Etc...

An excel, you would probably have a table with a column that said Family Type.   The list of all the family types TX, GA, BC, etc.    Selecting TX, would then (maybe using vlookup) bring up all of the relative cables that went with that family type.   3 could indicate a 3 foot cable, but if the SKU was TX-6, that would then indicate it was part of the TX family, but a 6 foot cable was in this SKU.    

G might be a 270V battery.   But H could indicate 320V.    So, TX-6-H  =   Family is TX....cable is 6 foot....battery is 320V....etc....

To complicate things the nomenclature does not always follow the same length.  So you can't just do a simple colum split using a delimiter.     

What I need to do is to be able to have a SKU pull up....then match all of the independent parts of that SKU (as mentioned above) return with their available surplus.    So...a search for TX-6-H...would pull up the available inventory for TX...and the available inventory for 6.....and then H.....and if all were available  then manufacturing could know they had all of the components needed to build this SKU.