Hello to the members of the site. I have already received a completely appropriate answer to my question from this site.
Is it possible to convert table one to table two with the help of power query? :
When the amount of the request for one customer and one product reaches 40, the request should be divided into two lines so that the price of the lot up to the amount of 40 is 200 and the rest of the request is inserted in the next line.
These phrases were translated into English by Google translator. I apologize if it is incomprehensible. Please pay more attention to the example for a better understanding of my request.
Please clarify why, for instance, the three transactions for Customer AA, Product P1 with amounts (I believe you mean quantities) of 10, 20 and 30 (totaling 60) need to be split into 10, 25, 5, 25 (totaling 65)?
Based on your question I would expect the split to become 10, 20, 10 for a total of 40 and then one more for 20 to arrive at the total of 60. By the way, none of the other pairings of customer and Product seem to make sense either.
Furthermore, the price seems to decrease once the quantity of 40 is exceeded. Anything more you can say about that?
Hello and thank you for your reply.
Yes, I know I'm not good at using words to get my Purpose. I will explain again: whenever the number of requests for one customer and one product reaches 40 ,After that the price will change and decrease for quantities greater than 40 and the request will be divided into two parts with different Price (Please see request number 3 in two tables)
The total for one customer and one product is not important. The important thing is that when the quantity reaches 40, the price changes greater than 40 and The request is divided into two rows
I'm sorry but don't understand. If you want to do something like this you need to have some logic that can be applied over and over again. I don't see that logic right now.
Thank You
Hi,P
Request 1 on 12/10/2023, the amount was 10 items. That is reflected in the Price table. Request 2, the amount was 20 items. However, in the Price table the amount Request 2 was 25 items. How did you come up with that figure?
Cedric
Hello . You are completely right . I corrected this
It wasn't all that straight-forward and I'm not sure it my solution works correct all the time. Though, it does work with your example data.
Although I could have added custom columns in PQ, I chose to add some helper columns in Excel as I found that easier to work with. See if this would work for you.
Hello, unfortunately, I do not see the attached file.Do not worry about the complexity of the codes, I will try to understand them. Please write the codes so that all steps occur in Power Query
Ooops. Forgot to attach the file.
Thank you, but I didn't understand. Maybe you should have sent another file. I saw the source step. I thought you started from the result .Thank you for your time
Don't know what happened there. The file somehow got corrupted. I recreated a version that should work. I trust you can 'translate' the Excel formulas into PQ yourself, if you really want to.
Attaching a new file.
Thank you