March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
This can all be done in Power Query. If you load each table into Power Query, you can follow these steps
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
Thank you so much ! Brilliant
This can all be done in Power Query. If you load each table into Power Query, you can follow these steps
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?
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 ?
You need to click the expand arrows next to the MLS table header, then only choose to keep the columns you need
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
147 | |
92 | |
70 | |
58 |