Forum Discussion

osama_ayoub's avatar
osama_ayoub
Helper III
3 years ago
Solved

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 onl...
Need Help
  • jbwtp's avatar
    jbwtp
    3 years ago

    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 res

    It gets two parameters:

    • t - a table of quanties available for this product,
    • req - required quantity

    In the funciton:

    1. avail takes the first row of the table of available materials passed to the funciton
    2. then we check if the available quantity on this row is sufficient to fulfill the required quantity passed to the function:
      1. If this is sufficient, it adds a field (which later converted to ta column in the output table) and sets its value = req
      2. 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
      3. 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

osama_ayoub's avatar
osama_ayoub
Helper III
3 years ago

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 CodeMaterial CodeMaterial DescriptionAvailable in Material CodeTotal Quantity Requiered from Planning Code
Main0035702-MAMS6586-CC27MS6586  DVB-T2/S2 T.MS6586.U783D<0117464600100
Main00357LRTEPL929KS0Main BD_50US9500E N1 (PANDA)23100
Main002702-MQ9E2B-C398000Main-BD50ES9500E4545
Main0010TRTEPK918GZ0Main board 32EL8250(LG- SLA3) TORNADO30150
Main0010TRTEPL603JW0PCB_UNIT_MAIN_3IN1_32EL8250E-B(SLA9)80150
Main0010TRTEPL603JV0PCB_UNIT_MAIN_3IN1_32EL8250E-B(SMA8)100150
Main0010TRTEPLZ21KZ0PCB_UNIT_MAIN_3IN1_32EL8250E-B_F56_TC161150
Main0010TRTEPL817KB0PCB_UNIT_MAIN_3IN1_32EL8250E-B(C’SOT OC)312150
Main0012LRTDPM809MM0PCB_UNIT_MAIN_32ER9500ECSOTA07(E20458)640100
Main0035LRTEPL104HW0PCB_UNIT_MAIN_3IN1_32ES9500E CSOT V0715050
Main0074CTTEPJ815DW0Main_BD_40L2800EV22
Main0081HTTEPM210LE0Main Board 32L5995 E1 (BOE)2036500
Main00163HTTEPKY06HG0PCB_UNIT_MAIN_50U59656850
Main00175HTTEPKY06HJ0PCB_UNIT_MAIN_65U5965110110
Main00318NNTEPL620KH0PCB_UNIT_MAIN_55X7500H1010
Main00358SPTDPM527LP0PCB_UNIT_MAIN_3IN1_4T-C55DL6EX_LG_OC3030
Main00365SPTEPL901KP0PCB_UNIT_MAIN_3IN1_4T-C50DL6EX_INX81100
Main00365SPTEPL901KP1PCB_UNIT_MAIN_3IN1_4T-C50DL6EX_INX_NEW P200100
Main00359SPTEPL901KQ0PCB_UNIT_MAIN_3IN1_4T-C55DL6EX_BOE150
Main00359SPTEPL901KQ1PCB_UNIT_MAIN_3IN1_4T-C55DL6EX_BOE_NEW P31450
Main00363SRTEPL901KL0PCB_UNIT_MAIN_3IN1_50UA1400E_INX125200
Main00363SRTEPL901KL1PCB_UNIT_MAIN_3IN1_50UA1400E_INX_NEW PNs397200
Main0080HTTEPKY17HN0PCB_UNIT_MAIN_3IN1_32L5995750250
Main0073HTTEPM210LD0Main BD_32L3965EA E1 (BOE)748200
Main0027LRTEPK117FK0PCB_UNIT_MAIN_50ES9500E105100
Main003STTEPL304JG0PCB MAIN_32L2600 (LG-V18) SKY TOSHIBA26350
  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    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

    • osama_ayoub's avatar
      osama_ayoub
      Helper III
      3 years ago

      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.

      • jbwtp's avatar
        jbwtp
        Memorable Member
        3 years ago

        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 res

        It gets two parameters:

        • t - a table of quanties available for this product,
        • req - required quantity

        In the funciton:

        1. avail takes the first row of the table of available materials passed to the funciton
        2. then we check if the available quantity on this row is sufficient to fulfill the required quantity passed to the function:
          1. If this is sufficient, it adds a field (which later converted to ta column in the output table) and sets its value = req
          2. 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
          3. 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