Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
asmith7
New Member

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 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 1Part 1 Description021
Part 2Part 2 Description010
Part 3Part 3 Description336

 

MSL Table Example

 

Part Number 1Part DescriptionSite 1 MSLSite 2 MSLSite 3 MSL

Part 1Part 1 Description121
Part 2Part 2 Description030
Part 3Part 3 Description538

 

Any help massively appreciated

 

Amanda

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

View solution in original post

7 REPLIES 7
asmith7
New Member

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

asmith7
New Member

Thank you so much ! Brilliant

johnt75
Super User
Super User

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

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?

 

asmith7_0-1647269933962.png

Thanks

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

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

 

asmith7_0-1647337322991.png

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.