Forum Discussion
customize column to choose from duplicated rows in power Query
Hi,
I have a table like below column A [Planning Code] have some of family codes and this family codes are sometimes shared between different codes in column[Material Code] now I want to package only the quantity in coumn[Required from planning Code] from each [Material Code]or if it iw not available I want to package the available quantity , the problem is there is duplicate values in column [Planning Code] , I want to only to choose the code or some of codes which complete the required quantity of Planning Code ?
Regards
Hi osama_ayoub,
As you merged the tables PBI allocated a set of records for each matching line in the source table (this is why it is important/more convenient to merge Requests with Avail rather then vise versa):
if you click on the cell (not on the Table itself) in the Available column you will see how the available quantities are assigned to the requested ones.
Then we add a column that is actually a set of the available quantities with how many of them we can allocate to fullfill the request. The majic happens in this fuction:
f = (t as table, req as number)=> let avail = Number.From(t{0}[Available in Material Code]), res = if avail > req or Table.RowCount(t) = 1 then {Record.AddField(t{0}, "take", List.Min({req, avail}))} else {Record.AddField(t{0}, "take", avail)} & @f(Table.Skip(t), req - avail) in resIt gets two parameters:
- t - a table of quanties available for this product,
- req - required quantity
In the funciton:
- avail takes the first row of the table of available materials passed to the funciton
- then we check if the available quantity on this row is sufficient to fulfill the required quantity passed to the function:
- If this is sufficient, it adds a field (which later converted to ta column in the output table) and sets its value = req
- If there is only one row left in the column, there is no point going any further and we just allocate the lesser of req and avail , i.e. trying to fullfill the request as much as possible
- Otherwise, we take as much as we can on this "supply" row and call the function again, this time passing all but the first line in the "supply" table (cause this is already been "taken") and the portion of "required" amount (reduced by the volumes fullfuilled by this "supply" row.
This may be a bit unclear, but it hopefully it can make sense if you "walk the steps" in the function, bearing in mind that it recursively calls itself with demonishing number of lines and required quantities until either "supply" rows will be depleted or the entire required amount supplied.
Kind regards,
John
9 Replies
osama_ayoub can you give me an example of what you want? I am having trouble understanding your requirements based on the text. Does the material column come into play?
For example, in Main0057, does this qualify or not, because the 02* material has enough quantity, but the LR* item does not. So is the entier planning code Main057 a fail or pass?
Also, can you provide actual data to work with? I cannot work with an image and I'm not typing all of that in. Instructions to paste data in a table format are below. An image of a desired result you have mocked up in Excel is fine and can be very helpful. Thanks!
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.Hi,
The requiered quantity from Main00357 is 100, So there are 2 material Code under the same planning Code: the first row has 600 so this is enough and we don't need to take any quantity from the second row which has the same planning code Main00357.
another example:
the planning code Main0010 available in 5 different Material Code or 5 different rows and all we want from these 5 rows are 150 so we go for the the first row and check the availble balance is it 150 or more we take 150 from it and we will not take any other rows but if the balance in less than 150 we took the availble quantity and move to the next row and so on until we complete our 150 unit.
thank you for your support
Regards
Planning Code Material Code Material Description Available in Material Code Total Quantity Requiered from Planning Code Main00357 02-MAMS6586-CC27 MS6586 DVB-T2/S2 T.MS6586.U783D<0117464 600 100 Main00357 LRTEPL929KS0 Main BD_50US9500E N1 (PANDA) 23 100 Main0027 02-MQ9E2B-C398000 Main-BD50ES9500E 45 45 Main0010 TRTEPK918GZ0 Main board 32EL8250(LG- SLA3) TORNADO 30 150 Main0010 TRTEPL603JW0 PCB_UNIT_MAIN_3IN1_32EL8250E-B(SLA9) 80 150 Main0010 TRTEPL603JV0 PCB_UNIT_MAIN_3IN1_32EL8250E-B(SMA8) 100 150 Main0010 TRTEPLZ21KZ0 PCB_UNIT_MAIN_3IN1_32EL8250E-B_F56_TC 161 150 Main0010 TRTEPL817KB0 PCB_UNIT_MAIN_3IN1_32EL8250E-B(C’SOT OC) 312 150 Main0012 LRTDPM809MM0 PCB_UNIT_MAIN_32ER9500ECSOTA07(E20458) 640 100 Main0035 LRTEPL104HW0 PCB_UNIT_MAIN_3IN1_32ES9500E CSOT V07 150 50 Main0074 CTTEPJ815DW0 Main_BD_40L2800EV 2 2 Main0081 HTTEPM210LE0 Main Board 32L5995 E1 (BOE) 2036 500 Main00163 HTTEPKY06HG0 PCB_UNIT_MAIN_50U5965 68 50 Main00175 HTTEPKY06HJ0 PCB_UNIT_MAIN_65U5965 110 110 Main00318 NNTEPL620KH0 PCB_UNIT_MAIN_55X7500H 10 10 Main00358 SPTDPM527LP0 PCB_UNIT_MAIN_3IN1_4T-C55DL6EX_LG_OC 30 30 Main00365 SPTEPL901KP0 PCB_UNIT_MAIN_3IN1_4T-C50DL6EX_INX 81 100 Main00365 SPTEPL901KP1 PCB_UNIT_MAIN_3IN1_4T-C50DL6EX_INX_NEW P 200 100 Main00359 SPTEPL901KQ0 PCB_UNIT_MAIN_3IN1_4T-C55DL6EX_BOE 1 50 Main00359 SPTEPL901KQ1 PCB_UNIT_MAIN_3IN1_4T-C55DL6EX_BOE_NEW P 314 50 Main00363 SRTEPL901KL0 PCB_UNIT_MAIN_3IN1_50UA1400E_INX 125 200 Main00363 SRTEPL901KL1 PCB_UNIT_MAIN_3IN1_50UA1400E_INX_NEW PNs 397 200 Main0080 HTTEPKY17HN0 PCB_UNIT_MAIN_3IN1_32L5995 750 250 Main0073 HTTEPM210LD0 Main BD_32L3965EA E1 (BOE) 748 200 Main0027 LRTEPK117FK0 PCB_UNIT_MAIN_50ES9500E 105 100 Main003 STTEPL304JG0 PCB MAIN_32L2600 (LG-V18) SKY TOSHIBA 263 50 Hi osama_ayoub,
This is possible to wrinte a code that does what you need, but I guess looking a bit wider you are joining the available and planned tables on the Planning Code. Is this right? In this case assuming that you have two original tables, it would look like this:
let Plan = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY+7DcAgDER3cU3hDwYyRCZAKVKmyf5lMBIShrRPz3fnWuG8nxdRNEMAQoQrDMaGok6E0CSdJeLtTrQhJ+XYCE+gUFdcUJL1jLIlEblwKr3Q9RkSh5L+zDrWAumd7LRiP7Kfv1tj6/UB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Planning Code" = _t, #"Total Quantity Requiered from Planning Code" = _t]), Avail = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZXNcpswEIBfReOTPRPSXQkJceRHMTYCE0Ocv2aYtL300s6003tfo6/XJ+kKGxsS2/URSftptfpWPD9Pitev3wCEDCZXE+BeERW1klp5ScLd0PaLsXQTew3/UHPWXG/Hru8CLdKPvyj4MyAGvvJpvQKYvFyNsXbdmMqGPMxrcEiaYnHaSrirQwlgWIlsWkVlGs1omosBgPdp3YaGx14iQg3QQ7w4lWC2DBry5SAQ3aLGbZyHqOdP+40/fX/98YUJbqzmEqZ27rHaRmLGmtW6jNIVrRNwFGQViOW9+6ySuL0rF01bRIuyFYsS2x5ovHhKvNCdRJ/hbC7hFJF2HIQToCeO+dP/Qe2NVG2TOJLC4ySNQR5fkFLy9/efetWwVeISE8iHOL6967QqNIRFcQTHzbq7rYQYEQRTw8GX3RmVP/Zmrw2Cn50p+s4gB2QbcLagHJICJ2XSEGmpUab3vQctCeiD5aST2TjrBjEaaSBzMQVHsOYg7c4dK8NQMkPWxivTOQtCDSuhRE/IH0Fl8/f5k/wyVO6YSg8jAzmKXL6PVLKPRBzVDDWNlWVnGIc8O7KpfAioXFnn1KjcLrSu3NVJHtjqRL39xkukTK0yD62dt6vkbbOILi8CuX4HzM+CYAtalA+uV/AcBi/CtKW5Z1V3HePThSPa7QWno3t1RTpHOZfTgbLPSaA/OqEzpF73NHsiJ9IkQp8c3ZUJuTxHOZHTiLLNqPzpcgqDofZwUA+DrDzZdJ3+NBmMO23vfNc16fCppxhBzppo2DSBr9++9F3L5/QzucmPtczhrUcY1cFVwe1sBfjLvtvY7s2xnH5KzL30G9QzVueP9NbX2SKOnCpUwpeXfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Planning Code" = _t, #"Material Code" = _t, #"Material Description" = _t, #"Available in Material Code" = _t]), f = (t as table, req as number)=> let avail = Number.From(t{0}[Available in Material Code]), res = if avail > req or Table.RowCount(t) = 1 then {Record.AddField(t{0}, "take", List.Min({req, avail}))} else {Record.AddField(t{0}, "take", avail)} & @f(Table.Skip(t), req - avail) in res, #"Merged Queries" = Table.NestedJoin(Plan, {"Planning Code"}, Avail, {"Planning Code"}, "Available", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Table.FromRecords(f([Available], Number.From([Total Quantity Requiered from Planning Code])))), #"Removed Columns" = Table.Combine(#"Added Custom"[Custom]) in #"Removed Columns"Unless the number of material codes per planning code is not overwhelming (otherwise the recurrent function will start be a bottleneck), this should work Ok.
Kind regards,
John
you are right I merged two tables (required and availble) based on planning code , could you tell me what are the steps you did to understand your solution?
Thank you so much for your support.
hi,
for the planning code Main00357 the required quantity is 100 and there are 2 rows under the same planning code so we go to the first row and check the available if it is equal or greater than the required quantity we take the required quantity from first row and skip the second row but if the quantity in the second row is less than the required we take all the quantity in the first row and go to the second row and take it untill we complete our required quantity.
I hope I could explain it well
Regards
Again:
- Need data
- Need example of your desired output. You know the old saying, a picture is worth a thousand words.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.https://docs.google.com/spreadsheets/d/1_UWWE5pZdhdgIsmCxueDqt_RjP01V816/edit#gid=1335661612
I have attached excel file, what I want is a new column to determine how much should I take from [available in Material code] to get the [required from planning code]
there are duplicated values in column [required from planning code] so I want only one value from this duplicated value.
thanks for trying to help me
Regards
