Log In
Register
Home
New Member
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
Trusted Members
Moderators
January 31, 2022
Offline
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
1 Guest(s)
