Log In
Register
Home
March 19, 2024
Offline
Basically - those lines having the same "Uniq ID" in column G are not supposed to overlap in time - column H and I. Those that do so anyway, PQ is to return.
I have a dataset with lot of customer prices, each price have a valid date range. By mistake some items have more than 1 price for the same customer in same periode. I need to identify these in order to shut one down of them. I have used Power query for ETL and are looking to finalize this job in PQ. Guess that is possible.
I have created a column "Uniq ID" which contains customer, item, quantity and pack info. So this is the only column to validate against price periodes. I would love it if, the query removes all prices that are fine, and only returned those to handle.
I have tried for weeks
but can't seem to find the right way to handle this date issue. Hoping for your assisstance.
File attached.
BR
Susanne
See Answer
Moderators
January 31, 2022
Online
There is no attachment. Please try again and don't forget to press "Start upload" before you submit your reply.
March 19, 2024
Offline
Oh - I did receive a site error when posting, that must have been the xlsx failing - I was afraid it was the post itself...
Trying once more., attached
BR
Susanne
Moderators
January 31, 2022
Online
OK! Thanks for the file. But I don't understand the logic of what you wan to do. You say that there are overlapping periods for items with the same ID.
Why would you only want to keep ID's with ItemA in both cases and remove ID's with ItemB and ItemC?
And then the Items B and C seem to have different suppliers (don't see any reference to customers). Wouldn't different suppliers have different prices in the same period?
Please clarify.
March 19, 2024
Offline
The customer is mentioned in column C and is named 17 / A3. But I have concatenated the relevant combinations of data, so it is only the "Uniq ID" that is to be tested against the dates.
There should be no date overlap on lines with matching "Uniq ID"s
ItemB has only one entry with customer 17 and one entry with customer A3 therefore no clashing here. The same on ItemC. Item A on the other hand has two entries on customer A3 with open dates = overlap, and three entries on customer 17 which also overlaps in the dates.
I have done the concatenate as there is 4 things to match up on, customer, item, quantity and packings. When concatenated it is just the one value to test.
Please ask again if not clear
Moderators
January 31, 2022
Online
See attached! Though still not sure I understood all. How do you define an overlap?
Perhaps I'm just slow today 🙂
March 19, 2024
Offline
Or maybe I'm just too much into my data to translate them 🙂
When changing the uniq IDs it seems to work properly, as the output updates accordingly.
But if I change the dates something is not quite good. If adding 31/3 as end date in line 9, line 9 and 10 no longer conflicts. They have the same Uniq ID, but dates are no longer overlapping and should therefor be removed from the output.
I think my definintion is pretty clear 
Lines with the same uniq ID must not conflict on the dates. In other words no date can be included more than once per Uniq ID.
Moderators
January 31, 2022
Online
I see my query is not giving the results you expect, but I still don't understand the logic. Sorry. Let me explain why.
Customer 17, Item A has three entries. The first spans from 1 March to 1 May (row 2) The other two have only start dates. 1 February (row 5) and 1 April (row 6). I would say that the 1 April item is overlapping with the first one but the 1 February item is not as its start date is before 1 March.
For customer A3, Item A you originally hade two rows with different start dates, no end dates. So I would say there is no overlap.
The logic I see emerging is that potentially every row that does not have an end date is suspect and needs to be investigated. Why do you say that rows 9 and 10 are no longer overlapping when adding the end date in row 9? Why are rows 1 and 5 overlapping then? Is it just because row 5 has a date before the start date in row 1 and row 10 has a start date after the end date of row 9?
March 19, 2024
Offline
I think we are back to the definition - and end dates. A price with no end date are valid "forever", and the price line is active on all future dates. So, for Customer 17, Item A, all three entries are a problem.
Line 2: 1 March to 1 May
Line 5: 1 February to "forever"
Line 6: 1 April to "forever"
Meaning
In February there is 1 valid price
In March there is 2 valid prices
In April there is 3 valid prices
in May and forward there is 2 valid prices
If there is only 1 line and it has no end date this is no problem, the price is ongoing.
Moderators
January 31, 2022
Online
OK, that makes everything clear.
See attached! I'm not particularly proud over the query as it is rather crude and I believe there must be a better way.
For all items without an ending date, I added a date very far in future so that I could sort and check each start date to an item's previous end date. Let me know if this is what you had in mind.
In any way, it needs some cleaning up and it would be better if you rename the steps to something more meaningful. I haven't gone that far. Sorry!
Answers Post
March 19, 2024
Offline
Perfect - I have added the query to my master file and it seems to work perfect. Even better is, that I have learned something from this - e.g. to make use of grouping on such problems. So thanks for all help 
1 Guest(s)
