Forum Discussion

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
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:
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

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

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
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:
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
- osama_ayoub
  Helper III
  3 years ago
  Thank you so much for your support.

Forum Discussion

customize column to choose from duplicated rows in power Query

Related content

Merge duplicates data in columns

Column count duplicates

Duplicate ranking

Add duplicate row based on column

Custom Column