locked
Excel table refresh issue RRS feed

  • Question

  • Good Afternoon,

    I recently posed this question to the SQL forum but was unable to resolve the issue.   Apparently this file is not actually a SQL file.   I created this spreadsheet a year ago by watching lots of youtube videos and a little help on this forum, so I created a monster I'm not exactly 100% proficient in.  So here is my problem:

    I have a database for ordering and budget that I have been using for the past year and it has worked great for our department (no issues).   My boss wanted to rename the products (our original file was very sloppy and unorganized).  To do this, I reopened the original file, and changed the names of the items in the original table...previously, we have been able to rename items individually and haven't experienced this issue.  We just waited to update all the names with the new fiscal year because renaming a row resulted in deleting data from the expanded columns subsequent queries.  So looking at the spreadsheet, we edit "Items Master" and it updates "Orders".   The "Orders" table has 52 additional columns for each fiscal week that is connected to the linked query (but not on "Items Master").  

    This is an example of the Items Master spreadsheet we manually changed the item names on

    Item Item Number Pkg Count Store Category Food Type
    Brown Sugar, Packet 809330 96 GFS Grocery Condiments
    Chia     Trader Joes Grocery Condiments
    Cocoa Powder Each Kroger Grocery Condiments
    Cream Cheese, Single Serve 257737 100 GFS Grocery Condiments
    Flax   Kroger Grocery Condiments
    Flour, Whole Wheat   5-lb bag Kroger Grocery Condiments
    Honey Packets 270539 1000 GFS Grocery Condiments
    Hot Cocoa Mix   GFS Grocery Condiments
    Jelly, Grape, Bulk 224111 6 GFS Grocery Condiments
    Jelly, Strawberry, Bulk 224081 6 GFS Grocery Condiments
    Ketchup, Bottle 214551 16-14oz bottle GFS Grocery Condiments
    Ketchup, Packets 852406   GFS Grocery Condiments
    Mayo 700011 200 GFS Grocery Condiments

    When the data refreshes, it appears only items with a value in "Item Number" duplicate, and they multiply each time you refresh (the column values have shifted over when I copy/pasted, but it won't let me add a screen snip to this post but I've made these rows in bold to highlight the problem)

    Teams Item Item Number Pkg Count Store Category Index Food Type
    Nutter Cocoa Powder Each Kroger Grocery 2 Condiments
    Nutter Flax Kroger Grocery 4 Condiments
    Nutter Flour, Whole Wheat 5-lb bag Kroger Grocery 5 Condiments
    Nutter Peanut Butter, Bulk 1111073932 4 lbs Kroger Grocery 14 Condiments
    Nutter Peanut Butter, Bulk 1111073932 4 lbs Kroger Grocery 14 Condiments
    Nutter Peanut Butter, Bulk 1111073932 4 lbs Kroger Grocery 14 Condiments
    Nutter Peanut Butter, Bulk 1111073932 4 lbs Kroger Grocery 14 Condiments
    Nutter Peanut Butter, Single Serve, Jif Natural 8 1.5 oz cup Kroger Grocery 17 Condiments

    Any idea why it would be happening with the new spreadsheet?   Again, I haven't changed the commands at all and the name changes were relatively minor, so "Peanut Butter, Bulk" used to be "Bulk Peanut Butter".   All other columns with corresponding data for each row would have remained identical. 

    Here is a link to the spreadsheet and additional background about the flow of the document

    https://1drv.ms/x/s!Ao7p3TYttj1XljT0dcYBO-IBxqF1

    Items Master: Master database

    Orders: Used to enter orders for each team each week (pulls line items from items master and has 52 expanded columns attached)

    Prices: Used to enter prices (and subsequently calculate spending), we need this sheet because we don't have fixed pricing

    Weekly Cost Allocation: Putting it all together

    Thank you!

    Monday, July 9, 2018 2:48 PM

Answers

  • Hi Shae. I was looking at your second spreadsheet. It sounds like you may be running into issues with manually modifying intermediate results loaded to the worksheet, and then not having those update properly when the underlying data is refreshed. My high level advice would be to try to do everything you need to do in PQ, and then (and only then) load the final result back to a worksheet.

    Ehren

    Wednesday, July 18, 2018 9:11 PM

All replies

  • Hi Shae. Are you editing the values in the source data that PQ is pulling from, or in the loaded results?

    Ehren

    Thursday, July 12, 2018 8:05 PM
  • Hi Ehren,

    A little of both?  The issues started arising when we began to edit the original source (Items Master).   We have used this same spreadsheet with same queries and done this before without any issues.   For some reason it is now.   Once the original query loads to "Orders" we then edit values in that table as well - which queries further on down the line.  But this issue seems to be occurring once we edit the original query and before we add any later data.

    Sunday, July 15, 2018 1:52 AM
  • So I think I actually was able to recreate this sheet from scratch and I believe I'm only missing one key component.  I don't know how to add the "teams" column in.   The way our budget works is that we order everything on one invoice but bill various teams independently.   So the "Kroger" order for 7/15 might be a total of $100 divided as $30 on yogurts, $20 on granola bars and $50 on plates...but in reality it might be 3 separate invoices that will divide up after (say $40 to Football, $40 to MBB, $20 to MSOC).   The original spreadsheet somehow converted items master into a series of repeating rows (1 of each row from items master for each team on orders) that we then added a slicer to organize which teams we were ordering for.   

    Football and MBB may both order vanilla yogurt, so when we are entering our order all we need to know is total ordered (ex. 20) and once we are invoiced we split it again by team (ex. 15 @ $1.00 and 5 @ $1.00)

    Does what I'm needed to do make sense?  And are you/am I able to do it on this new spreadsheet (see below) - it's structured somewhat different from the original.

    LINK:

    https://1drv.ms/x/s!Ao7p3TYttj1XlkMMyE7yeYYB1tCJ

    Thank you!

    Monday, July 16, 2018 2:44 AM
  • Hi Shae. Based on looking at your spreadsheet, I'd recommend you try the following:

    • The Orders query seems to be redundant. It's loading data from the Items Master sheet, and then writing it back out to the Orders sheet. I'd recommend disabling the loading of the Orders query. (See the next bullet for how it can be used.)
    • In the Cost query, there's no reason to reference the Orders data loaded to the worksheet. Instead, just reference the Orders query directly (i.e., in the Source step of Cost, instead of = Excel.CurrentWorkbook(){[Name="Orders"]}[Content], just use = Orders).
    • If you need to fix up any product names, do so in the ultimate source data (i.e. the Items Master sheet). Don't hand-modify the data in the sheets that are loaded from PQ queries.
    • Adding the Teams column sounds like a Merge operation, but I'm not sure what the criteria would be.

    Ehren

    Monday, July 16, 2018 6:44 PM
  • Thank you for your help.   Were you on my first spreadsheet or the second link I uploaded yesterday?

    I tried to start from scratch just to hopefully work around whatever issues that original sheet has developed (the duplication of items upon refresh).  The original link in my first post is "perfect" for what I need except the duplication issue.

    Upon playing with my "new" worksheet more (I uploaded the link in my last reply) I realized that it actually wasn't right.    The problem with that one is that the quantity ordered in date columns (on orders tab) doesn't link itself with the corresponding item.  For example, If I add a row to items master at line 54, the item in 54 moves to line 55, but the order history (in the extended columns) remains in 54.   I also just tried to stretch it out 52 weeks (Columns named "week 1", "week 2", ....) and create a new query and merge that with Orders (using Right outer merge) but that didn't seem to fix the problem either.

    Sorry I'm not very good at communicating my issues/solving my own problems!  I spent about 4 hours today trying to google how to fix this myself but am just stumped!

    Tuesday, July 17, 2018 2:32 AM
  • Hi Shae. I was looking at your second spreadsheet. It sounds like you may be running into issues with manually modifying intermediate results loaded to the worksheet, and then not having those update properly when the underlying data is refreshed. My high level advice would be to try to do everything you need to do in PQ, and then (and only then) load the final result back to a worksheet.

    Ehren

    Wednesday, July 18, 2018 9:11 PM