Compare Current Stock table against Min and Max Levels table and produce results table

[asmith7]

Hi All, I'm hoping someone can help.  Fairly new to this.  I have 2 tables.  Table 1 has a list of parts, their site locations and then the amount in stock.  Table 2 has the minimum stock levels for each of these parts, at each site (As it may vary from site to site) I want to compare table 1 to table 2 and then have a new table that is produced that only shows any of the items that are below their minimum stock level for that particular site.  Totally not sure where to start with this and wondered if anyone had any advice or has done anything similar ?

Stock Level Table example

Part Number 1Part DescriptionSite 1 StockSite 2 StockSite 3 Stock

Part 1	Part 1 Description	0	2	1
Part 2	Part 2 Description	0	1	0
Part 3	Part 3 Description	3	3	6

MSL Table Example

Part Number 1Part DescriptionSite 1 MSLSite 2 MSLSite 3 MSL

Part 1	Part 1 Description	1	2	1
Part 2	Part 2 Description	0	3	0
Part 3	Part 3 Description	5	3	8

Any help massively appreciated

Amanda

[asmith7]

Apologies, I hadn't expanded the merged query.  All sorted now thanks

[asmith7]

Thank you so much ! Brilliant

[johnt75 · Accepted solution]

This can all be done in Power Query. If you load each table into Power Query, you can follow these steps

1. rename the Site columns in both tables to just be "Site 1", "Site 2" etc, 
2. select the Part number and Part description columns, right-click and choose "Unpivot other columns"
3. rename the new columns as Site and either MLS or StockLevel depending on which table you're in.
4. merge the 2 queries, use ctrl-click to select both the Part Number and Site columns in both tables. only keep the MLS or StockLevel column from the 2nd table
5. add a conditional column called "Below threshold" which returns 1 if the StockLevel column is < the MLS column

Once you've loaded the data into Power BI you can use the Below Threshold column as a filter to only show Sites and Parts which are below the minimum level

[asmith7]

Hi @johnt75 I'm trying to add the conditional column but it is wanting a value rather than referring to the MSL table.  Screenshot attached, any ideas?

Thanks

[johnt75]

did you merge the queries? the merged table should have a column for both stock level and MLS

[asmith7]

I did but when merged it just has a column titled table ?

[johnt75]

You need to click the expand arrows next to the MLS table header, then only choose to keep the columns you need