Forum Discussion
Solved
Keep First 80%
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é"
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"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
9 Replies
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"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 CombinedFndufoq3 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.
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.
You're welcome 😉
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 TeamIf 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
Anonymous 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 AThanks for your time and for your suggestion
