Forum Discussion

Einomi's avatar
Einomi
Helper V
2 years ago
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 combi...
General Comment
Need Help
Show and Tell
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    2 years ago

     

    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's avatar
    dufoq3
    2 years ago

    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
lbendlin's avatar
lbendlin
Super User
2 years ago

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's avatar
dufoq3
Community Champion
2 years ago

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.