Forum Discussion
Solved
Compare Current Stock table against Min and Max Levels table and produce results table
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 ...
This can all be done in Power Query. If you load each table into Power Query, you can follow these steps
- rename the Site columns in both tables to just be "Site 1", "Site 2" etc,
- select the Part number and Part description columns, right-click and choose "Unpivot other columns"
- rename the new columns as Site and either MLS or StockLevel depending on which table you're in.
- 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
- 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
Apologies, I hadn't expanded the merged query. All sorted now thanks