+ Reply to Thread
Results 1 to 4 of 4

I want to use Power Query to keep track of Inventory

  1. #1
    Registered User
    Join Date
    11-29-2017
    Location
    Delaware
    MS-Off Ver
    2013
    Posts
    13

    I want to use Power Query to keep track of Inventory

    I want to use Power Query to combine about 3000 excel invoices that I generate each month at work and give me total of each item sold. The information that I need for it to combine is on the third worksheet named "Inv" (see attached document) and I can't seem to figure out how to get it to combine the data from it. The first sheet "Invoice" has the same data but also has a lot of merged cells and unnecessary information so I simplified it to 2 Columns on the 3rd sheet "Inv" to just reflect the merchandise and quantities that are entered on the first sheet"Invoice". My goal is to take all 3000 invoices each month and have it add up the quantities for each items so I can see how many we sold and then have it subtract from our in stock count. Are there ways of doing that without using power query? When I add column in power query, is there a specific column formula that I need to enter? All invoices that I want to combine have different names but the format is constant LastName FirstName dd.mm.yyyy.xlsm.
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: I want to use Power Query to keep track of Inventory

    I assume you have all files in a single folder?

    1. Make sure your file that you are using PQ reside outside of the folder.

    2. Then use PQ From File->From Folder. This should give you table with list of files in given folder.

    3. Now go to Home tab in Edit mode. Find "Combine" and click on it.

    4. This will give you list of sheets/tables in given file. Click on INV sheet and hit OK.

    PQ will auto create custom functions and parameters needed to combine files and will generate combined table.

    If you are not happy with the result, go to query named "Transform xxxx from..." and apply any necessary transformation.

    You can then load the result to data model and report using PivotTable.

    Edit: See link. I found more detailed tutorial.
    https://www.myonlinetraininghub.com/...-from-a-folder
    Last edited by CK76; 05-31-2018 at 10:05 AM. Reason: See Edit:
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    11-29-2017
    Location
    Delaware
    MS-Off Ver
    2013
    Posts
    13

    Re: I want to use Power Query to keep track of Inventory

    Thank you. Do you know know of any other way I could combine data from multiple spreadsheets? A different program or a website perhaps?

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: I want to use Power Query to keep track of Inventory

    Sure, there are number of ways to do it.

    If file keeps same structure across...
    1. ADODB connection using VBA. Though this will be bit difficult to manage if you are not familiar with it.

    2. Ron de Bruin's RDBMerge add-in. I have not used it myself, but I've heard good things from those that have.
    https://www.rondebruin.nl/win/addins/rdbmerge.htm

    3. MS Query - Similar to PowerQuery, but has outdated GUI and is bit of pain to edit.

    If it does not have same structure... there's no easy way of merging workbooks. Good data structure is essential for data manipulation and analysis.

    There are other options, but I primarily stick to ADO & PowerQuery myself.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sub-Forum for Power Query & Power BI?
    By AliGW in forum Suggestions for Improvement
    Replies: 7
    Last Post: 08-26-2018, 05:25 PM
  2. Replies: 0
    Last Post: 04-05-2018, 01:16 AM
  3. error in power query & power pivot
    By Baldev Kumar in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-03-2018, 01:34 AM
  4. Excel 2007 : Track Inventory
    By Driankeith in forum Excel General
    Replies: 8
    Last Post: 10-28-2010, 04:12 PM
  5. Track inventory
    By cporter5 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2010, 05:11 AM
  6. How to track inventory?
    By rjez in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-12-2006, 05:05 PM

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