topic Compare Current Stock table against Min and Max Levels table and produce results table in Desktop

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

asmith7 — Wed, 09 Mar 2022 15:23:22 GMT

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

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

johnt75 — Wed, 09 Mar 2022 16:26:39 GMT

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

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

asmith7 — Fri, 11 Mar 2022 14:18:59 GMT

Thank you so much ! Brilliant

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

asmith7 — Mon, 14 Mar 2022 14:59:06 GMT

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

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

johnt75 — Mon, 14 Mar 2022 15:05:54 GMT

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

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

asmith7 — Tue, 15 Mar 2022 09:42:12 GMT

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

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

johnt75 — Tue, 15 Mar 2022 09:47:05 GMT

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

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

asmith7 — Tue, 15 Mar 2022 09:47:45 GMT

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