Results 1 to 5 of 5

Power Query - Excluding Data from one PQ table using another "Exclusion Table"

Threaded View

  1. #1
    Registered User
    Join Date
    03-21-2022
    Location
    Leominster, MA, USA
    MS-Off Ver
    Office 365
    Posts
    10

    Power Query - Excluding Data from one PQ table using another "Exclusion Table"

    #This issue needs to be solved in Power Query#

    I have a power query table, "INVENTORY" with a list of inventory (One row for each item I have in inventory, i.e. duplicate values). I have a second table, "COMMITTED INVENTORY" which lists the SKUs of inventory that has been committed to a client and is not available to be sold. (Also one row for each item I have in inventory, i.e. duplicates)

    I need to adjust my PQ inventory query to filter out the SKUs in the Committed Inventory table.

    Properties of the INVENTORY table:
    Every product in inventory is a single row. So if I have a quantity of 6 of one SKU, it is repeated 6 times.
    Each duplicate SKU could have a different cost.

    Fields in Inventory: SKU | DESCRIPTION | COST | PRICE

    Properties of the COMMITTED INVENTORY table
    This table has one column, "SKU"
    This table could have duplicates

    Fields in Committed Inventory: SKU


    Both tables are Power Query Tables

    Logic
    Each SKU in needs to be processed 1 by 1 (I think??)
    If SKU#1 in "Committed Inventory" table exists in inventory, remove the row with the lowest "cost" field.
    Move to next...


    For some reason I could not attach a file or an image or a link to the post. Not sure what's goin on there. If anyone can help, I'll figure out a way to get the sample data to you. I tried to explain the scenario as best as I can.

    Here is basically a CSV of sample data since I can't attach a file
    INVENTORY TABLE
    SKU,Description,Cost,Price
    SKU10001, "Product 1",50.00,80.00
    SKU10001, "Product 1",45.00,80.00
    SKU10001, "Product 1",50.00,80.00
    SKU10001, "Product 1",50.00,80.00
    SKU10001, "Product 1",30.00,80.00
    SKU10001, "Product 1",50.00,80.00
    SKU10001, "Product 1",45.00,80.00
    SKU10002, "Product 2",125.00,150.00
    SKU10003, "Product 3",100.00,175.00
    SKU10004, "Product 4",57.00,90.00
    SKU10005, "Product 5",64.00,110.00
    SKU10006, "Product 6",129.00,200.00
    SKU10006, "Product 6",139.00,200.00
    SKU10006, "Product 6",129.00,200.00
    SKU10006, "Product 6",129.00,200.00
    SKU10007, "Product 7",55.00,85.00
    SKU10008, "Product 8",75.00,115.00

    COMMITTED INVENTORY TABLE
    SKU
    SKU10001
    SKU10001
    SKU10001
    SKU10006
    SKU10006
    SKU10007

    DESIRED FILTERED INVENTORY
    SKU,Description,Cost,Price
    SKU10001, "Product 1",50.00,80.00
    SKU10001, "Product 1",50.00,80.00
    SKU10001, "Product 1",50.00,80.00
    SKU10001, "Product 1",50.00,80.00
    SKU10002, "Product 2",125.00,150.00
    SKU10003, "Product 3",100.00,175.00
    SKU10004, "Product 4",57.00,90.00
    SKU10005, "Product 5",64.00,110.00
    SKU10006, "Product 6",129.00,200.00
    SKU10006, "Product 6",139.00,200.00
    SKU10008, "Product 8",75.00,115.00
    Last edited by JNSDBTC; 05-09-2022 at 07:03 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Power Pivot Data Model Extra "Query" tabs
    By trisoldee in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-17-2019, 11:37 AM
  2. Replies: 6
    Last Post: 02-17-2017, 08:31 AM
  3. [SOLVED] How to Create Table excluding "FALSE" data?
    By Esthil in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-30-2015, 05:59 PM
  4. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  5. "text" query table filtering data
    By husein_786 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-12-2013, 06:09 AM
  6. Replies: 2
    Last Post: 12-25-2012, 08:49 AM
  7. Pivot table "Show all data" query
    By Laurence Lombard in forum Excel General
    Replies: 2
    Last Post: 01-31-2006, 05:24 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1