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

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.

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

Can you share your file? It's difficult to envisage what you're doing exactly.

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

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

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.

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.

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

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.

I'll get back to you tomorrow (my time). At CET 9PM now.   

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.

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.

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.