Keep First 80%

[Einomi]

Hi,

I am responsible for auditing, monitoring and reducing costs of our suppliers. We got fruit and vegs from the market. I have managed to get a daily .csv report of what we buy. So I have combinated through Power Query, and I have a table with the date, item, quantity and price. I have data for a year. So far so good 🙂

The issue is that we do not get always the same "brands" of vegs. For example, I have 11 differents brands for cucumbers and this is true for many other products such as Aubergines, Peppers, Onions etc.

My goal is to have a list of fruits and vegs we buy, with the average price (over the last 6 or 12 months) based on the brands I order the most.

So I created a "mapping" table, that lists all the products and I added a column to input the catergory. For example, in this table I have my 11 different cucumbers brands in one column, and in another column I have all labeled them as "Cucumbers", besides for each row I have the number how many this brand was received, and the frequency compared to the catergory. For example,

Item_Received	Mapping_Category	How_Many_Received	Freq
Cues Spanish	Cucumbers	25	3.9%
Cues Neth	Cucumbers	312	49.1%
Cucumbers Fr	Cucumbers	98	15.4%
Cues 12/ IRL	Cucumbers	201	31.6%

I need PQ to sort the fequency in a descending order, then I want to keep all the rows that totalling the at least first 80% of the category. In that example I want PQ to keep only "Cues Neth" and "Cues 12/ IRL" because together the represent more than 80% of the cucumbers I receive.

Obviously, this table contains more fruit and vegs so the M code has to work for all categories

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVldc6M6Ev0r1DztVs2HJPQBjxgTmwoGFuzJTd29DyRhHdc4dhbbd2b212+DJAxGdpxcUtc1VTpHLan7dLf055+fvDSNgvzLKPOC0SKLc+ur9fXT50+Ewv/QZ0wpE46NbfjX5rBew89NdVjt4dd7fV2Xu09/fW45Jl7knYend5fBSTQOYgVnpIEDkDAbYXKcXf0Y8JkXx/d6eianZ4gjQvHbs6dhfGtF3lgT2EgSOEgQ4rjkEsNiFGSTMA701jm4wSKXc+YIjIfGHx7KarnaDAnGh/3js0Ua86mioTA9R5i+m8ZG9SIokkdBHYIRIrb7Dp5gs1yvds+Wj497iojr2MS1+Tt48tdiU/PMahpHyLOlpDbpTZbvie+Nk9Yt7fooDKi/t4/F01ZiRt5tGE+sNJl788T6RZECC+UViDObInfIkm73xX5bapYYPu2Qjpq25wWjYgOfGh54cf7lBtarINho6KgsNhoQzLMkmevhtjgDKPfVdrtXoGgRwLZmYettRHCjaetDCRtZrfRqMm8WBfeW9HeFdeWJMkowd7i4GOO+Nxp5k8CaFtWThRH8/VjW07vSTQX4hCuoM1yAXzw8FEsTC0Hw17DY0hAkGOO8OaYrOLLySe4cknDCXJAbhw4j5QSe5qG/mOXWF2uSBa3mUGUEBFvzDb2jfH0tq3MkMuaOJwJ+xgVpvncy6WiRXiGjFzmYNN/VXLPwj2Csl6a2l9DmeyfHcWVKFRF25PdOot7ClO9hzOrvepuynkUgIfLsXbk97+PR+iZPTRnE7eZ7F1F3ZYSq3EGZ/K5mug+iKLnrehJTnsSY3Kb3UnUXqGNMHt31G6Wo+l7JlVde51FZBjL3xRoFuVY7giSDwzCkJM6HWckvqkqrXsvgT714HsRem6bV/lBHcGTbJvHpsARRkIWer03AZoX2y3VZrYrHDui+3cjGwbj0XYaEzYhLz1GUZgaqZgUCM/B3F6a3vZlYBQ22XYGYYzhD08QtA23ipWGQdmMx9PKLDPxK06dhFIWdNFi7HQU1dNyhFvrPq/W6nbBBNtXcUZiZXDURTCDk2oapjRRZK3+YSQEU2OWEQ6p7g+F59Xe501hkdpJmjJowCa1Rcnsx7fvP25U12v6QiCiYBfG8UzJiYw731+VLudm3tZC/Bb/cPJWV3hjzVHqUxCRQLHjxOMg06EwFBbA+bpFNgvm8NZFh5wzuUC3L/V7buGgRWnCgeCWIGLLy4fHw8tCqRA1k6BtH1ngx96dtlEqBr6tF6nLXIBTnaIJ4EoW5JnKk54PPg/ND+FzDw9E3wXrmfGBNgn1z+2vSIUEgKhAyeOOQ5N8HGC4m8sfKUy/urEyVtQJBVctNFdg5vpmR7wNr/AUFYZ9EJYiGAdGr1vgL0/42HQsyBr3LlYbQk3Mnqmapdxphg+CZSU5Wo7bYVsa8wTL2jnGjq/peZI8LHS9jEB69XnNg1iPk0MybJLF1ky3CNo2aEVWx3G4azE0Qx0F0cfRNudmUa2nNTTjJLw9eLeXIiZdFoU6lZ1qXSVGtVyqPQiM4aTXINQ9fbZZ6B6GbTwMr/ZZao8jzW21VmcDlTtMk9PZ0UhWv5Sm6m0dsGXegI/aV6E4KkRmE4MsTT5MsDzJv3Akm2l/r97Lud6bbaldWxRNkVYUM/bqvm6jir806dRw6FEobPuAIH+sGb9mHt1VGTcCQkk7wewd1+7trGMintkEUjIPnd4v0CwRttdCYIHWAynrFkLsvUriXdsFEcBvehb0AaWOqd2K3q58rBUimUeaNQnPylqu83T6vq+JBIaIguM07CbiWFhdBZWEQ8qgsf3RRo+0vqQkyFwlOT+s4OeMpLi73Xf/HDieCsoGhXdgs0W7PkWlRUfmy3XQHg9fnJ5d/yGUcC2YbsRa4/c5EkM9D/7YmUR2Vi6gNfcLA+4Ys8/nCD774SVeCmo6sdl5iD7a3d/TRvT8N9N0LMR579PvxuVTxNvPiSWL9I5yl/7woSrNis9xqSBbmVhqmoGPqbqmfzzFEGmWGJq17rTQLoiRumn0vCr1OkjijLrNyrQ+qhd5BftFaqq7lhIPJRWw47httMwlkiEJNbPNheu2bvdrsO/WBYaNggBy5yKdZkkDvOCp3e4utH3Y1AGG5TY6wEYM6rrNN0h9mh91ztd2+7E5Y0qp8LR5/KBmWO4UJd6mNULtibUSPI/bGnp/M22Spo7u3R3HxVDxu96pHjAN/Dsd8rMyNhXlcPu6Lqq3Lkzisz6VuIspiY/2ykNXEnKP817Zd4rBh65NsVvp4BhTYaqylKuIxckCCh5s2pJAJBAgE/DWXa1hVQEhQKMW6Pd+bHA78NRztDZIrOHavZyAU/uQVn7KCEJtAoLyDw7Xc3iWhZBio0pAg/l9XRiA8Hae3hWePAfLP6ngIVN3n2YxiDv8NlOw8AbGa+wL1nEEdhrHbq4aHBBkI07GFMrpfUoEoKd+ry5Q7L59CpXI3DedBP8iJqn7tjjiYojv1sry+Hb5ZF3srhzpsXcIQRvX0PWvTotqty98tMA7TVrG5fQ6xWb3qufI8PK1jmWmdKaQG2Bir+bcCB16vjRpiyuJR11QwOMshn8Q3QRbEftAtySAvCXBDCEsTRdUlSKEAnXqzvtg3SOnG58DT+xwkvpdTDde3z793xVrVFmlUX7kpK40pLF0flLilySyo37wgFXRr1CFi+1IuwWHajqMGRm0WsM9h1to3Mm82Wsy9uJOUh8vIipeHw76QTUddNvvTMLg5yeOQHR3sDvN455WhhqoY0osyFtD13X8nanJvni9mXm4u49RPXux3hxf1XJOnWf1G1J+LS4lxGQgMtwU9nTV/BdFf9ibu0ajivcYp7adcODYnA801MP1rAWH8VZvimFad//dQtB1DDonq7V4wh0T1n/pUJSTz7o7PR3+oPkPV6I78hgKZ76viZ/cxacDTyIzq121XfvTUq96mqRetHo7JufeOIcsCelErj71WhaiuePvTH5ZFZeWbQulQfheMjxdwxHy7lf8sn8p2fDBX8tpKCToLg5K9J7Hz598v5UX/bEbIscmsniRvbrxhf/R1N0bSrQhjLqFYGDi2L50pBzTfj8+T+jFUCEEgLQ2bl3NUszAOrVqpumzKh5AtXEy5O7yZPsfW5aDq1R+KUSiYDO+zZpL+rZXAyEWG6z0zdlSulysouGafjjWOS4XDxVCmzAyd1zGVGoRNGSSX4VXTJYLZh5egG/aPL6Fl+DhF94XfVnovmENc2xk2Fj0KVbzUwv/xpup8BcS0MbZz5lmv5fnr/w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Items = _t, Freq = _t, #"Freq_%" = _t, #"Main Seller" = _t, Category = _t, FV = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Items", type text}, {"Freq", Int64.Type}, {"Freq_%", Int64.Type}, {"Main Seller", type text}, {"Category", type text}, {"FV", type text}})
in
    #"Type modifié"

@m_dekorte @vijay @BA_Pete 

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZhbk6I4FID/Smqedqu2ZxDx9hgwItXcluD0dM3OA21nlRoEF3Vmen/9JiGJoQXc0uouPZ8nJznX8PXrBxjHPsIPdgKRvUlCDD6Cjx/++GBa9A88Hgty+vDtD4W50Id3iMhfolAwE7ObSWAYPks9k04m9sJH4MOlxMbGO2xjo8T1QiQtno8YcHkh9S4vb6Hl5bzdA5OvZv0fdGywFS3DGmRRuSvy0x44I7WVXhYfs5KxAUPnsw72c+TAZaR8MObH96PaZq9VQ9jw0QtdEEcpTCPwyzIEOmMrx9U5O1dEkiF9ST/M6R87K+lLSBEM8cOKmiUIZr5NslKKUZpEUSqF4xkXk3NdVWeB+BtEd5Z4ygHmbMqo4kLopupc2pHAwEfPoHGrQBdtXzrQtqGLwDqrX8HIoO/vO6ZwwaxyspeXbNdFmgZ9c3LcxyXktdmB0YHE2HM2AQYPwE2QilmLkTE5HkndBzYBct3zMC3d3pykNcAH3he0lGbc5a5W8NwYhltGLAbwpKXZmpt3WJkAzWncgXUrTGvI6mfk+9GTftKToZMWuG7MkBsF3vbMrf4koSnwAGyEZSaYxpTHUV3LPFCUs4ZhikKoCtYNiXyUeNCRqkY8uklB6jzbasiz2saIHf50oTDSTVkifzn0piNyg1zRqE+Roizu8XvUtGO5tef7nlZKnH1eFEoBl/K6f02zyaIXS1QSjCbWDbXPf5CTlBuN+IdSEXnAjh5bllQ5sKvvjdxHAQpTrWvw3TgFOZDyrOqwU1GXlK+klrYyRn7XEBGtfDBcokQivFZTqE1tEhelqVptMuKnW13qHTmf5WobJedF6rK9HF5UEDLhxPg0NcBykzprFTzmEIpC1/ewhOfzbnZqfJpNWmp71p9NPi3a64+6wb8uhmHO3OYfwDEMNSsmQ78JOn/TY88v2iPa4LQPtNpm9yq03p2ZafST75a+3dcSXl3OSuIyk75e0kiWtjAPss+NIIFuFIJVsvFUrWHyOttVJSdWKAyRr8lWpCxJ0ehdeS7WRfmu+d6Fie/JisO7uJvVRS7KDR1LXBXBLB/dvNzJTdAhLUYg/hQD24eOSimO1dmRvKf05B6P+igtt602s44SjBK41LzPiHVVn0idvdLSI0DPYYOHK3oDLwFsOW/L5o5dm1E1klET4y5mioPopFQF5Mqm97BFl2GP3pPX8jJf8jH/mQt5tPYTaOu19LHaF3X2IgAfoUes1SWfkO+6xK5+NXE37xCG5Hz1oi4MIum6qcFFh6rUZdSBuHU14ASg7jt1YTj1nEeG8onglk3TjYMenEiP2dYx+c/OGsnZ1GRi/227JyICAhi6EfjNC+LfteANsnJXSSDxMIi9mEa3mJWv59kakwPkRyGf1aDvQS1nA1LII1DME81rmS581tchb9leajy5mcmDvDxrlY19bL7f4DUdt+loYpPTGUyKlxNvbnxCCC6nPR27D6d3aFyTY7b9LnJpdkOGcAmdKFXVhOkKs9dsW53FQBIiJ6UHdW2GLKRDsj1ntWqFUeixvbMWTG8H4BcwAE84Fl1Rmcvd32AjMOJ2LTq5JnUpNaNvPsHznBoA5/TNQWuQMy36bq4EwwoXYKFuDrdU+K8Ky1shze/8ukWL3wD6KRPwIW7yXhe9ArvXvs911DSAxbGzUvkE8ZpWy6e1l6J2aJm8O7VCK4YJZtesVZGdAaZlvCCsQFh8qM3qU0HeFBd6sUq86VgAZX6UmjD23rciHso0f6n9YFVfchFBMYKtkSAm2VaWaSpLMM3xcIUSFDpIL+qUq3Uqpg1mDYO2JkmsnzHNzVb1ifdvp6wQFTH22VAvtPPrSnERORBHAWIPG2je6l0nrg5kR09btWXG+SpxxwIp5OHSK6y9SWGoVaskO7xczlnTmllTo9MrWmmAdr1lYhEV0ghmBbubahGBYYo3AcTaIeDsfLocxJUdxwm7+rcVcffhI83XXUtXixWNkq0578b/3NBY+yiVcuifS6Y6LqZl4v1kgmmV+JsGggAS+HR9GvBFdOUp5+rsp/5A4Iblwcwns/vsXFSAW3JDRx2AQ6hCm18v8WWX1QCXmQhu/ISW19nf5Dv9SV6JkqJUpJqKV0NAtHW2Mi7dvx2I5iz+uZFEAVOA+aWQGi9vhCOuKq0OmpIb9PP1mQx/qNOHB17oARb6+i/4iff9Qucssxe89qduuU2KHb3j8KdYVi+lPZwY3YGCweXkaDa8nKKGMf0J3PjmCETBZek6PDT0V2Ze5q/st/8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Items = _t, Freq = _t, FV = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Freq", Int64.Type}},"fr-FR"),
    process = (tbl) =>
      let 
        #"Sorted Rows" = Table.Sort(tbl,{{"Freq", Order.Descending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
        #"Added Custom" = Table.AddColumn(#"Added Index", "First80", each try if List.Sum(List.FirstN(#"Added Index"[Freq],[Index]))/List.Sum(#"Added Index"[Freq])<.8 then true else false otherwise true)
    in
        #"Added Custom",
    #"Grouped Rows" = Table.Group(#"Changed Type", {"FV"}, {{"Rows", each process(_)}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Items", "Freq", "First80"}, {"Items", "Freq", "First80"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Rows",{{"Items", type text}, {"Freq", Int64.Type}, {"First80", type logical}})
in
   #"Changed Type1"

[dufoq3 · Accepted solution]

Hi @Einomi, similar approach as @lbendlin, but with list generate (this one should be faster)

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVldc6M6Ev0r1DztVs2HJPQBjxgTmwoGFuzJTd29DyRhHdc4dhbbd2b212+DJAxGdpxcUtc1VTpHLan7dLf055+fvDSNgvzLKPOC0SKLc+ur9fXT50+Ewv/QZ0wpE46NbfjX5rBew89NdVjt4dd7fV2Xu09/fW45Jl7knYend5fBSTQOYgVnpIEDkDAbYXKcXf0Y8JkXx/d6eianZ4gjQvHbs6dhfGtF3lgT2EgSOEgQ4rjkEsNiFGSTMA701jm4wSKXc+YIjIfGHx7KarnaDAnGh/3js0Ua86mioTA9R5i+m8ZG9SIokkdBHYIRIrb7Dp5gs1yvds+Wj497iojr2MS1+Tt48tdiU/PMahpHyLOlpDbpTZbvie+Nk9Yt7fooDKi/t4/F01ZiRt5tGE+sNJl788T6RZECC+UViDObInfIkm73xX5bapYYPu2Qjpq25wWjYgOfGh54cf7lBtarINho6KgsNhoQzLMkmevhtjgDKPfVdrtXoGgRwLZmYettRHCjaetDCRtZrfRqMm8WBfeW9HeFdeWJMkowd7i4GOO+Nxp5k8CaFtWThRH8/VjW07vSTQX4hCuoM1yAXzw8FEsTC0Hw17DY0hAkGOO8OaYrOLLySe4cknDCXJAbhw4j5QSe5qG/mOXWF2uSBa3mUGUEBFvzDb2jfH0tq3MkMuaOJwJ+xgVpvncy6WiRXiGjFzmYNN/VXLPwj2Csl6a2l9DmeyfHcWVKFRF25PdOot7ClO9hzOrvepuynkUgIfLsXbk97+PR+iZPTRnE7eZ7F1F3ZYSq3EGZ/K5mug+iKLnrehJTnsSY3Kb3UnUXqGNMHt31G6Wo+l7JlVde51FZBjL3xRoFuVY7giSDwzCkJM6HWckvqkqrXsvgT714HsRem6bV/lBHcGTbJvHpsARRkIWer03AZoX2y3VZrYrHDui+3cjGwbj0XYaEzYhLz1GUZgaqZgUCM/B3F6a3vZlYBQ22XYGYYzhD08QtA23ipWGQdmMx9PKLDPxK06dhFIWdNFi7HQU1dNyhFvrPq/W6nbBBNtXcUZiZXDURTCDk2oapjRRZK3+YSQEU2OWEQ6p7g+F59Xe501hkdpJmjJowCa1Rcnsx7fvP25U12v6QiCiYBfG8UzJiYw731+VLudm3tZC/Bb/cPJWV3hjzVHqUxCRQLHjxOMg06EwFBbA+bpFNgvm8NZFh5wzuUC3L/V7buGgRWnCgeCWIGLLy4fHw8tCqRA1k6BtH1ngx96dtlEqBr6tF6nLXIBTnaIJ4EoW5JnKk54PPg/ND+FzDw9E3wXrmfGBNgn1z+2vSIUEgKhAyeOOQ5N8HGC4m8sfKUy/urEyVtQJBVctNFdg5vpmR7wNr/AUFYZ9EJYiGAdGr1vgL0/42HQsyBr3LlYbQk3Mnqmapdxphg+CZSU5Wo7bYVsa8wTL2jnGjq/peZI8LHS9jEB69XnNg1iPk0MybJLF1ky3CNo2aEVWx3G4azE0Qx0F0cfRNudmUa2nNTTjJLw9eLeXIiZdFoU6lZ1qXSVGtVyqPQiM4aTXINQ9fbZZ6B6GbTwMr/ZZao8jzW21VmcDlTtMk9PZ0UhWv5Sm6m0dsGXegI/aV6E4KkRmE4MsTT5MsDzJv3Akm2l/r97Lud6bbaldWxRNkVYUM/bqvm6jir806dRw6FEobPuAIH+sGb9mHt1VGTcCQkk7wewd1+7trGMintkEUjIPnd4v0CwRttdCYIHWAynrFkLsvUriXdsFEcBvehb0AaWOqd2K3q58rBUimUeaNQnPylqu83T6vq+JBIaIguM07CbiWFhdBZWEQ8qgsf3RRo+0vqQkyFwlOT+s4OeMpLi73Xf/HDieCsoGhXdgs0W7PkWlRUfmy3XQHg9fnJ5d/yGUcC2YbsRa4/c5EkM9D/7YmUR2Vi6gNfcLA+4Ys8/nCD774SVeCmo6sdl5iD7a3d/TRvT8N9N0LMR579PvxuVTxNvPiSWL9I5yl/7woSrNis9xqSBbmVhqmoGPqbqmfzzFEGmWGJq17rTQLoiRumn0vCr1OkjijLrNyrQ+qhd5BftFaqq7lhIPJRWw47httMwlkiEJNbPNheu2bvdrsO/WBYaNggBy5yKdZkkDvOCp3e4utH3Y1AGG5TY6wEYM6rrNN0h9mh91ztd2+7E5Y0qp8LR5/KBmWO4UJd6mNULtibUSPI/bGnp/M22Spo7u3R3HxVDxu96pHjAN/Dsd8rMyNhXlcPu6Lqq3Lkzisz6VuIspiY/2ykNXEnKP817Zd4rBh65NsVvp4BhTYaqylKuIxckCCh5s2pJAJBAgE/DWXa1hVQEhQKMW6Pd+bHA78NRztDZIrOHavZyAU/uQVn7KCEJtAoLyDw7Xc3iWhZBio0pAg/l9XRiA8Hae3hWePAfLP6ngIVN3n2YxiDv8NlOw8AbGa+wL1nEEdhrHbq4aHBBkI07GFMrpfUoEoKd+ry5Q7L59CpXI3DedBP8iJqn7tjjiYojv1sry+Hb5ZF3srhzpsXcIQRvX0PWvTotqty98tMA7TVrG5fQ6xWb3qufI8PK1jmWmdKaQG2Bir+bcCB16vjRpiyuJR11QwOMshn8Q3QRbEftAtySAvCXBDCEsTRdUlSKEAnXqzvtg3SOnG58DT+xwkvpdTDde3z793xVrVFmlUX7kpK40pLF0flLilySyo37wgFXRr1CFi+1IuwWHajqMGRm0WsM9h1to3Mm82Wsy9uJOUh8vIipeHw76QTUddNvvTMLg5yeOQHR3sDvN455WhhqoY0osyFtD13X8nanJvni9mXm4u49RPXux3hxf1XJOnWf1G1J+LS4lxGQgMtwU9nTV/BdFf9ibu0ajivcYp7adcODYnA801MP1rAWH8VZvimFad//dQtB1DDonq7V4wh0T1n/pUJSTz7o7PR3+oPkPV6I78hgKZ76viZ/cxacDTyIzq121XfvTUq96mqRetHo7JufeOIcsCelErj71WhaiuePvTH5ZFZeWbQulQfheMjxdwxHy7lf8sn8p2fDBX8tpKCToLg5K9J7Hz598v5UX/bEbIscmsniRvbrxhf/R1N0bSrQhjLqFYGDi2L50pBzTfj8+T+jFUCEEgLQ2bl3NUszAOrVqpumzKh5AtXEy5O7yZPsfW5aDq1R+KUSiYDO+zZpL+rZXAyEWG6z0zdlSulysouGafjjWOS4XDxVCmzAyd1zGVGoRNGSSX4VXTJYLZh5egG/aPL6Fl+DhF94XfVnovmENc2xk2Fj0KVbzUwv/xpup8BcS0MbZz5lmv5fnr/w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Items = _t, Freq = _t, #"Freq_%" = _t, #"Main Seller" = _t, Category = _t, FV = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Freq", Int64.Type}, {"Freq_%", Percentage.Type}}),

    Fn_Keep80 =
        (myTable as table)=>
        [ 
            // _Detail = GroupedRows{[FV="Apples"]}[Fn],
            _Detail = myTable,
            _SortedRows = Table.Sort(_Detail,{{"Freq_%", Order.Descending}}),
            _FreqBuffered = List.Buffer(_SortedRows[Freq]),
            _FreqCategory = List.Sum(_FreqBuffered),
            _LG = List.Generate(
                ()=> [x = 0, y = _FreqBuffered{x} ],
                each [y] / _FreqCategory < 0.8,
                each [x = [x]+1, y = [y] + _FreqBuffered{x} ]
            ),
            _StepBack = _SortedRows,
            _KeptFirstRows = Table.FirstN(_StepBack, List.Count(_LG)+1)
        ][_KeptFirstRows],

    GroupedRows = Table.Group(ChangedType, {"FV"}, {{"Fn", Fn_Keep80, type table}}),
    CombinedFn = Table.Combine(GroupedRows[Fn])
in
    CombinedFn

[Einomi]

Perfect @dufoq3 thanks for your suggestion

Performance is indeed important 🙂

[dufoq3]

You're welcome 😉

[lbendlin]

@dufoq3 it's an interesting premise and worth exploring further.  I came up with another option that goes something like

if only value in list then true

else if running sum <= .8 then true

else if previous running sum < .8 then true

else false

Wonder if this can be reformulated to approach it from a "default of false" perspective.

[dufoq3]

There is huge difference in speed because your query calculates every single row but my query with list.generate calculates only rows where sum of highest freq is less than 80%. If you use table repeat 10 for your query - processing time on my PC is around 10 seconds (result = 7 613 rows). But if you use repeat 5000 for my query - processing time is around 4 seconds (result = 380 326 rows), but of course at the beginning you have to wait for reapeating table 5000 times which takes some time.

[v-cgao-msft]

Hi @Einomi ,

It seems that you want to implement Pareto/ABC analysis using PowerQuery M. Try the following steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVldc6M6Ev0r1DztVs2HJPQBjxgTmwoGFuzJTd29DyRhHdc4dhbbd2b212+DJAxGdpxcUtc1VTpHLan7dLf055+fvDSNgvzLKPOC0SKLc+ur9fXT50+Ewv/QZ0wpE46NbfjX5rBew89NdVjt4dd7fV2Xu09/fW45Jl7knYend5fBSTQOYgVnpIEDkDAbYXKcXf0Y8JkXx/d6eianZ4gjQvHbs6dhfGtF3lgT2EgSOEgQ4rjkEsNiFGSTMA701jm4wSKXc+YIjIfGHx7KarnaDAnGh/3js0Ua86mioTA9R5i+m8ZG9SIokkdBHYIRIrb7Dp5gs1yvds+Wj497iojr2MS1+Tt48tdiU/PMahpHyLOlpDbpTZbvie+Nk9Yt7fooDKi/t4/F01ZiRt5tGE+sNJl788T6RZECC+UViDObInfIkm73xX5bapYYPu2Qjpq25wWjYgOfGh54cf7lBtarINho6KgsNhoQzLMkmevhtjgDKPfVdrtXoGgRwLZmYettRHCjaetDCRtZrfRqMm8WBfeW9HeFdeWJMkowd7i4GOO+Nxp5k8CaFtWThRH8/VjW07vSTQX4hCuoM1yAXzw8FEsTC0Hw17DY0hAkGOO8OaYrOLLySe4cknDCXJAbhw4j5QSe5qG/mOXWF2uSBa3mUGUEBFvzDb2jfH0tq3MkMuaOJwJ+xgVpvncy6WiRXiGjFzmYNN/VXLPwj2Csl6a2l9DmeyfHcWVKFRF25PdOot7ClO9hzOrvepuynkUgIfLsXbk97+PR+iZPTRnE7eZ7F1F3ZYSq3EGZ/K5mug+iKLnrehJTnsSY3Kb3UnUXqGNMHt31G6Wo+l7JlVde51FZBjL3xRoFuVY7giSDwzCkJM6HWckvqkqrXsvgT714HsRem6bV/lBHcGTbJvHpsARRkIWer03AZoX2y3VZrYrHDui+3cjGwbj0XYaEzYhLz1GUZgaqZgUCM/B3F6a3vZlYBQ22XYGYYzhD08QtA23ipWGQdmMx9PKLDPxK06dhFIWdNFi7HQU1dNyhFvrPq/W6nbBBNtXcUZiZXDURTCDk2oapjRRZK3+YSQEU2OWEQ6p7g+F59Xe501hkdpJmjJowCa1Rcnsx7fvP25U12v6QiCiYBfG8UzJiYw731+VLudm3tZC/Bb/cPJWV3hjzVHqUxCRQLHjxOMg06EwFBbA+bpFNgvm8NZFh5wzuUC3L/V7buGgRWnCgeCWIGLLy4fHw8tCqRA1k6BtH1ngx96dtlEqBr6tF6nLXIBTnaIJ4EoW5JnKk54PPg/ND+FzDw9E3wXrmfGBNgn1z+2vSIUEgKhAyeOOQ5N8HGC4m8sfKUy/urEyVtQJBVctNFdg5vpmR7wNr/AUFYZ9EJYiGAdGr1vgL0/42HQsyBr3LlYbQk3Mnqmapdxphg+CZSU5Wo7bYVsa8wTL2jnGjq/peZI8LHS9jEB69XnNg1iPk0MybJLF1ky3CNo2aEVWx3G4azE0Qx0F0cfRNudmUa2nNTTjJLw9eLeXIiZdFoU6lZ1qXSVGtVyqPQiM4aTXINQ9fbZZ6B6GbTwMr/ZZao8jzW21VmcDlTtMk9PZ0UhWv5Sm6m0dsGXegI/aV6E4KkRmE4MsTT5MsDzJv3Akm2l/r97Lud6bbaldWxRNkVYUM/bqvm6jir806dRw6FEobPuAIH+sGb9mHt1VGTcCQkk7wewd1+7trGMintkEUjIPnd4v0CwRttdCYIHWAynrFkLsvUriXdsFEcBvehb0AaWOqd2K3q58rBUimUeaNQnPylqu83T6vq+JBIaIguM07CbiWFhdBZWEQ8qgsf3RRo+0vqQkyFwlOT+s4OeMpLi73Xf/HDieCsoGhXdgs0W7PkWlRUfmy3XQHg9fnJ5d/yGUcC2YbsRa4/c5EkM9D/7YmUR2Vi6gNfcLA+4Ys8/nCD774SVeCmo6sdl5iD7a3d/TRvT8N9N0LMR579PvxuVTxNvPiSWL9I5yl/7woSrNis9xqSBbmVhqmoGPqbqmfzzFEGmWGJq17rTQLoiRumn0vCr1OkjijLrNyrQ+qhd5BftFaqq7lhIPJRWw47httMwlkiEJNbPNheu2bvdrsO/WBYaNggBy5yKdZkkDvOCp3e4utH3Y1AGG5TY6wEYM6rrNN0h9mh91ztd2+7E5Y0qp8LR5/KBmWO4UJd6mNULtibUSPI/bGnp/M22Spo7u3R3HxVDxu96pHjAN/Dsd8rMyNhXlcPu6Lqq3Lkzisz6VuIspiY/2ykNXEnKP817Zd4rBh65NsVvp4BhTYaqylKuIxckCCh5s2pJAJBAgE/DWXa1hVQEhQKMW6Pd+bHA78NRztDZIrOHavZyAU/uQVn7KCEJtAoLyDw7Xc3iWhZBio0pAg/l9XRiA8Hae3hWePAfLP6ngIVN3n2YxiDv8NlOw8AbGa+wL1nEEdhrHbq4aHBBkI07GFMrpfUoEoKd+ry5Q7L59CpXI3DedBP8iJqn7tjjiYojv1sry+Hb5ZF3srhzpsXcIQRvX0PWvTotqty98tMA7TVrG5fQ6xWb3qufI8PK1jmWmdKaQG2Bir+bcCB16vjRpiyuJR11QwOMshn8Q3QRbEftAtySAvCXBDCEsTRdUlSKEAnXqzvtg3SOnG58DT+xwkvpdTDde3z793xVrVFmlUX7kpK40pLF0flLilySyo37wgFXRr1CFi+1IuwWHajqMGRm0WsM9h1to3Mm82Wsy9uJOUh8vIipeHw76QTUddNvvTMLg5yeOQHR3sDvN455WhhqoY0osyFtD13X8nanJvni9mXm4u49RPXux3hxf1XJOnWf1G1J+LS4lxGQgMtwU9nTV/BdFf9ibu0ajivcYp7adcODYnA801MP1rAWH8VZvimFad//dQtB1DDonq7V4wh0T1n/pUJSTz7o7PR3+oPkPV6I78hgKZ76viZ/cxacDTyIzq121XfvTUq96mqRetHo7JufeOIcsCelErj71WhaiuePvTH5ZFZeWbQulQfheMjxdwxHy7lf8sn8p2fDBX8tpKCToLg5K9J7Hz598v5UX/bEbIscmsniRvbrxhf/R1N0bSrQhjLqFYGDi2L50pBzTfj8+T+jFUCEEgLQ2bl3NUszAOrVqpumzKh5AtXEy5O7yZPsfW5aDq1R+KUSiYDO+zZpL+rZXAyEWG6z0zdlSulysouGafjjWOS4XDxVCmzAyd1zGVGoRNGSSX4VXTJYLZh5egG/aPL6Fl+DhF94XfVnovmENc2xk2Fj0KVbzUwv/xpup8BcS0MbZz5lmv5fnr/w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Items = _t, Freq = _t, #"Freq_%" = _t, #"Main Seller" = _t, Category = _t, FV = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Items", type text}, {"Freq", Int64.Type}, {"Freq_%", Int64.Type}, {"Main Seller", type text}, {"Category", type text}, {"FV", type text}}),
    #"Grouped Rows" = Table.Group(#"Type modifié", {"FV"}, {{"Data", each Table.AddIndexColumn(Table.Sort(_,{"Freq",Order.Descending}), "Index", 1, 1, Int64.Type)}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"Data", (x) => Table.AddColumn(x, "CategoryABC", each if List.Sum(List.FirstN(x[Freq], [Index])) / List.Sum(x[Freq]) <= 0.8 then "A" else "B")}),
    Custom2 = Table.TransformColumns(Custom1, {"Data", each Table.SelectRows(_, each [CategoryABC]="A")}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom2, "Data", {"Items", "Freq", "Freq_%", "Main Seller", "Category", "CategoryABC"}, {"Items", "Freq", "Freq_%", "Main Seller", "Category", "CategoryABC"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([CategoryABC] = "A"))
in
    #"Filtered Rows"

ABC classification – DAX Patterns

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Einomi]

@v-cgao-msft thanks for your suggestion

However, I need all the rows reaching at least 80% of the category, your solution keeps the rows before reaching the 80%
Also, when there is only one item in the category it returns B instead of A

Thanks for your time and for your suggestion

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZhbk6I4FID/Smqedqu2ZxDx9hgwItXcluD0dM3OA21nlRoEF3Vmen/9JiGJoQXc0uouPZ8nJznX8PXrBxjHPsIPdgKRvUlCDD6Cjx/++GBa9A88Hgty+vDtD4W50Id3iMhfolAwE7ObSWAYPks9k04m9sJH4MOlxMbGO2xjo8T1QiQtno8YcHkh9S4vb6Hl5bzdA5OvZv0fdGywFS3DGmRRuSvy0x44I7WVXhYfs5KxAUPnsw72c+TAZaR8MObH96PaZq9VQ9jw0QtdEEcpTCPwyzIEOmMrx9U5O1dEkiF9ST/M6R87K+lLSBEM8cOKmiUIZr5NslKKUZpEUSqF4xkXk3NdVWeB+BtEd5Z4ygHmbMqo4kLopupc2pHAwEfPoHGrQBdtXzrQtqGLwDqrX8HIoO/vO6ZwwaxyspeXbNdFmgZ9c3LcxyXktdmB0YHE2HM2AQYPwE2QilmLkTE5HkndBzYBct3zMC3d3pykNcAH3he0lGbc5a5W8NwYhltGLAbwpKXZmpt3WJkAzWncgXUrTGvI6mfk+9GTftKToZMWuG7MkBsF3vbMrf4koSnwAGyEZSaYxpTHUV3LPFCUs4ZhikKoCtYNiXyUeNCRqkY8uklB6jzbasiz2saIHf50oTDSTVkifzn0piNyg1zRqE+Roizu8XvUtGO5tef7nlZKnH1eFEoBl/K6f02zyaIXS1QSjCbWDbXPf5CTlBuN+IdSEXnAjh5bllQ5sKvvjdxHAQpTrWvw3TgFOZDyrOqwU1GXlK+klrYyRn7XEBGtfDBcokQivFZTqE1tEhelqVptMuKnW13qHTmf5WobJedF6rK9HF5UEDLhxPg0NcBykzprFTzmEIpC1/ewhOfzbnZqfJpNWmp71p9NPi3a64+6wb8uhmHO3OYfwDEMNSsmQ78JOn/TY88v2iPa4LQPtNpm9yq03p2ZafST75a+3dcSXl3OSuIyk75e0kiWtjAPss+NIIFuFIJVsvFUrWHyOttVJSdWKAyRr8lWpCxJ0ehdeS7WRfmu+d6Fie/JisO7uJvVRS7KDR1LXBXBLB/dvNzJTdAhLUYg/hQD24eOSimO1dmRvKf05B6P+igtt602s44SjBK41LzPiHVVn0idvdLSI0DPYYOHK3oDLwFsOW/L5o5dm1E1klET4y5mioPopFQF5Mqm97BFl2GP3pPX8jJf8jH/mQt5tPYTaOu19LHaF3X2IgAfoUes1SWfkO+6xK5+NXE37xCG5Hz1oi4MIum6qcFFh6rUZdSBuHU14ASg7jt1YTj1nEeG8onglk3TjYMenEiP2dYx+c/OGsnZ1GRi/227JyICAhi6EfjNC+LfteANsnJXSSDxMIi9mEa3mJWv59kakwPkRyGf1aDvQS1nA1LII1DME81rmS581tchb9leajy5mcmDvDxrlY19bL7f4DUdt+loYpPTGUyKlxNvbnxCCC6nPR27D6d3aFyTY7b9LnJpdkOGcAmdKFXVhOkKs9dsW53FQBIiJ6UHdW2GLKRDsj1ntWqFUeixvbMWTG8H4BcwAE84Fl1Rmcvd32AjMOJ2LTq5JnUpNaNvPsHznBoA5/TNQWuQMy36bq4EwwoXYKFuDrdU+K8Ky1shze/8ukWL3wD6KRPwIW7yXhe9ArvXvs911DSAxbGzUvkE8ZpWy6e1l6J2aJm8O7VCK4YJZtesVZGdAaZlvCCsQFh8qM3qU0HeFBd6sUq86VgAZX6UmjD23rciHso0f6n9YFVfchFBMYKtkSAm2VaWaSpLMM3xcIUSFDpIL+qUq3Uqpg1mDYO2JkmsnzHNzVb1ifdvp6wQFTH22VAvtPPrSnERORBHAWIPG2je6l0nrg5kR09btWXG+SpxxwIp5OHSK6y9SWGoVaskO7xczlnTmllTo9MrWmmAdr1lYhEV0ghmBbubahGBYYo3AcTaIeDsfLocxJUdxwm7+rcVcffhI83XXUtXixWNkq0578b/3NBY+yiVcuifS6Y6LqZl4v1kgmmV+JsGggAS+HR9GvBFdOUp5+rsp/5A4Iblwcwns/vsXFSAW3JDRx2AQ6hCm18v8WWX1QCXmQhu/ISW19nf5Dv9SV6JkqJUpJqKV0NAtHW2Mi7dvx2I5iz+uZFEAVOA+aWQGi9vhCOuKq0OmpIb9PP1mQx/qNOHB17oARb6+i/4iff9Qucssxe89qduuU2KHb3j8KdYVi+lPZwY3YGCweXkaDa8nKKGMf0J3PjmCETBZek6PDT0V2Ze5q/st/8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Items = _t, Freq = _t, FV = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Freq", Int64.Type}},"fr-FR"),
    process = (tbl) =>
      let 
        #"Sorted Rows" = Table.Sort(tbl,{{"Freq", Order.Descending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
        #"Added Custom" = Table.AddColumn(#"Added Index", "First80", each try if List.Sum(List.FirstN(#"Added Index"[Freq],[Index]))/List.Sum(#"Added Index"[Freq])<.8 then true else false otherwise true)
    in
        #"Added Custom",
    #"Grouped Rows" = Table.Group(#"Changed Type", {"FV"}, {{"Rows", each process(_)}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Items", "Freq", "First80"}, {"Items", "Freq", "First80"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Rows",{{"Items", type text}, {"Freq", Int64.Type}, {"First80", type logical}})
in
   #"Changed Type1"

[Einomi]

Thanks @lbendlin it answers my question and 🙂