Log In
Register
Home
September 10, 2022
Offline
Hi, I really need some help and not really sure where to start or what to search for?
I have a list of prices in an Excel table and have an updated table sent through every month which has additional rows, duplicate rows and updated prices. I would like to be able to get the current price list updated from the new listing but not sure if this need to be merged or appended or really where to start???
Can someone point me in a direction of a tutorial that will explain the basics and how I can go about doing this. I have tried searching for an answer but not really sure what I need to search to get the result I am after...sorry if this is a little basic but just not sure where to start.
The price list data is used in another table for creating quotes and invoices.
Thanks in advance
Moderators
January 31, 2022
Online
Hi Martin,
That requires a trick in Power Query where you append the new pricelist to the current list, remove duplicates, group, sort and keep the most recent price for each product.
The attached file contains a small example. The very first time you start with the New list (blue) and load it back to Excel and call the table Pricelist (green).
Now you have to edit the M-code and add a second source line. That's the one I called PL.
Next you append these two sources creating new entries, duplicates and it keeps all existing items that already were in PL. Remove the duplicates. So, you are appending the query to its previous output.
At this point you have what I would call a historic pricelist. All current and previous prices for all product in one table. Since you indicate to need only the current prices, group the table by Product. Now add a column that sorts the nested tables by date in descending order and keep only the first row of each table. Expand the lot and you'll end up with a price list containing the latest price per product, being a new price or an existing one if it did not change is in the latest update.
See if you can follow these steps. Change the New table and press Data, Refresh All and see what happens to the Pricelist table.
Riny
September 10, 2022
Offline
Hi Riny,
I am so sorry but I have only just seen your reply, a year late but still trying to sort this. Yes I see how it works but have no idea how you have implemented it. My listing does not have the current date in it but I guess that could esily be added.
I will try and work my way through this and see how I get on. Will keep you updated, thank you. Hopefully sooner than before.
September 10, 2022
Offline
Hi Riny,
I managed to get the data from the csv which is the new price listing which in your exmple is new and have added a column with todays date but then the listng you have as Price list I have a on tab how do I add that as the next step in the query? Basically I have a table that has the products, code price and now date, how do I go about adding the new csv date to this current list data???
Sorry
Moderators
January 31, 2022
Online
Can you share your file? It's difficult to envisage what you're doing exactly.
Trusted Members
October 17, 2018
Offline
Looking forward to see a file and the new pricelist file will help understand.
Don't forget to mention the Excel version you're using
September 10, 2022
Offline
Hi Riny, Hopefully attached ar the files.
The excel list is the table with the current list of items in and the csv file is the new list which is grabbed by FTP and downloaded. What I need to do is have the current listing (MillsListing) table update with the ARKMillsitems.csv data so it updates to the current price from the new listing but does not remove the previous items if they are not on the new listing and retain the row including the price.
Basically (I know that sounds simple), is update the current list with the new data, change the price if it has changed, and keep the current data if not on the new listing...if that makes sense.
You may also see that the csv file column headings are different to those that I have in my original data so that may also need to be addressed.
Sorry but I do not seem to be being notified when you reply despite being subscribed to the posting???
Thanks anyway.
Excel version is the latest on 365
September 10, 2022
Offline
I can work out how to get the csv listing, add a column to add todays date and then change the headings, but then the next step you have which is electing the original PL list I just can't see how to select that. Maybe that my data is on different sheets and not in the same worksheet.
Moderators
January 31, 2022
Online
Could get into the CSV only by right-clicking on it and then save as a file file. The system suggested it was an html file. I changed the extension to csv and it worke.
Now I'm not sure if you really want an historic price list with dates in it or just a current list based on the latest list plus changes from the CSV.
To avoid self referencing queries, which are a bit tricky to explain, perhaps easier to have query connect to the latest list. Connect another query (in the same workbook) to the CSV and do the correct formatting. Then merge 'latest' with 'CSV', (LeftOuter, based on Product Code). Expand the add a column that gives you the new price and perhaps another one to show the price change.
Next month, save the then valid prices in the same format as what you had the month before (same file name, same structure). Repeat the process described above.
In the attached file, I have extended your query with some steps that work with files on my system. It should work for you as well, provided that you point the ARKMillsitems query to the correct location of the CSV file on your system. No guarantees though.
Please come here again with some feedback.
September 10, 2022
Offline
Hi Riny,
I need the MillsListing.xlsx to be updated with the data from the csv listing so that it retains the previous items that may now not be in the current csv list, add new items that have been added and also update the prices which may have changed in the previous items listed. This listing is then referenced from another worksheet by xlookup to update the prices.
I hope that makes sense?
Ive added a xlms version of the ArkMillsitems csv file, hopefully that should work for you, this is normally a csv file which is downloaded via FTP.
Thanks
September 10, 2022
Offline
I cant access the query as it is asking for credentials to download but I can see the steps that you have taken.
I'll see if I can recreate from those steps.
Moderators
January 31, 2022
Online
I'll get back to you tomorrow (my time). At CET 9PM now.
Moderators
January 31, 2022
Online
Okay, the attached file contains a slightly different solution, but I believe it is what you asked for. The idea is that you connect PQ to the blue table (Current). Then you connect to the CSV which now sits in the ARKmillsitems tab for the queries to work in this file. All I do here is add todays date and set some data types.
The main query merges the Current list with the 'CSV' list and loads the end result back to the Orange table, the New price list.
Next time you can copy the contents of the orange table over the blue one, refresh the connection to the CSV and the orange table will be updated automatically.
September 10, 2022
Offline
Hi Riny,
Ive had a look but not sure how the second method is going to work. I have attached a diagram to try and explain but i'll see if this explains things.
The MillsListing is a worksheet in an excel workbook that also has a tab called price List and another called Quotes.
Every month the updated listing is downloaded as a csv file. This updated csv file has items that are on the current MillsListing list with the same prices, items with updated prices and also items that are no longer on the listing. The items that have been removed in the new listing I need to keep as they may still be used in the lookup but also the prices that have changed need to be updated.
I think your first method would be right but just trying to get my head around it and working. The second method seems to end up with two separate listings that my Price list tab with the lookups wont reference, is my understanding.
Sorry if I am confusing things.
September 10, 2022
Offline
I have tried working my way through your first example but get to the append stage which seems okay but not sure how to group the table by product.
Also, when I add the sources it adds a worksheet called MillsListing (2), will this then be renamed MillsListing or all the data will be added back to the original and delete this temporary worksheet.
I have added a cut down version of the workbook so you can see where I am at.
Moderators
January 31, 2022
Online
My understanding is as follows, referring to the data in your latest file:
1) You have a list of products with prices as per 1 August. I call this one "old".
2) You have a similar list as per 2 November. I call this one "new". It has item from "old" (but not all) with the same or updated prices and it has new items.
3) You need a combined list of "old" and ""new" containing the latest prices for all products that exist in both listings. I call this one "current".
4) Finally, you create a table called "PriceListTable" that links to various sources, on of which is "current".
The attached file contains a Sheet1 where I have "old", "new" and "current" side-by-side. The latter is created with PQ by appending "old" to "new" (i.e. not the same as "new" to "old") into a new query, with duplicates removed and sorted by product code. (In your real life situation you would connect both "old" and "new" to CSV files on your own system.)
My thought was that, next time, you clear-out "old", rename "new" to "old" and create a table "new" from the latest ARKMillsitems, and refresh the queries that will then create a new "current" table. Links to your PriceListTable should remain intact.
It's a far less complicated that setting up self referencing queries as I initially suggested.
1 Guest(s)
