Log In
Register
Home
March 13, 2022
Offline
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.
Moderators
January 31, 2022
Online
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?
March 13, 2022
Offline
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
Moderators
January 31, 2022
Online
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.
March 13, 2022
Offline
Thank You
January 20, 2019
Offline
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
March 13, 2022
Offline
Hello . You are completely right . I corrected this
Moderators
January 31, 2022
Online
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.
March 13, 2022
Offline
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
Moderators
January 31, 2022
Online
Ooops. Forgot to attach the file.
March 13, 2022
Offline
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
Moderators
January 31, 2022
Online
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.
1 Guest(s)
