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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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