Forum Discussion
Query to return results based on highest 80% of column value
Afternoon All,
I'm stuck on how to tackle a problem i've been presented. My source table contains rows of hypothetical opportunties that have been asigned a Most Likely Saving (if they were to be realised). My query already filters for Opps that are still to be realised but Im looking to further reduce the number of rows by only displaying the Opps that sit within the top 80% of the total ML Value. Just starting out on the Power Query journey and may have bitten off more than I can chew on this one.
let
Source = Excel.CurrentWorkbook(){[Name="Table_query__18"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name of efficiency / initiative", type text}, {"Detail of the idea", type text}, {"Project", type text}, {"Valid", type text}, {"Date Created", type datetime}, {"Opportunity type", type text}, {"Cost category", type text}, {"Idea Owner", type text}, {"Asset Type", type text}, {"Function", type text}, {"Discipline", type text}, {"BRAG Status", type text}, {"Fish bone category", type text}, {"McKinsey Theme", type text}, {"Speed Theme", type text}, {"Minimum saving obtainable", type number}, {"Most Likely Saving", Int64.Type}, {"Maximum saving obtainable", type number}, {"Target Implementation Date", type datetime}, {"Confidence Level", type number}, {"Saving already delivered from the efficiency", Int64.Type}, {"Target Cost to Implement", Int64.Type}, {"Actual Cost to Implement", Int64.Type}, {"Actual Implementation Date", type datetime}, {"Comments on current position providing some narrative of next steps (SharePoint to generate date of update)", type text}, {"Has a A3 / Business case been completed?", type text}, {"Link to Supporting Document (A3 / Business case etc)", type text}, {"Most likely completion date?", type any}, {"Sustainability Benefit", type text}, {"CO2 Saving", type any}, {"Delivery Unit", type text}, {"Person who generated the idea", type text}, {"Modified By", type text}, {"Modified", type datetime}, {"Created By", type text}, {"Legacy ID", type text}, {"Item Type", type text}, {"Path", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Valid] = "Yes") and ([BRAG Status] <> "Blue") and ([Cost category] = "Cost reduction")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Most Likely Saving", Order.Descending}})
in
#"Sorted Rows"
6 Replies
Hi davehus
Some example dummy data now included. Having scrubbed the source data i'm left with the example below. I'm then looking to return the same information but filtered/sorted to only show rows that fall in the top 80% of the total value of the Most Likely Saving Column. Doing this manually in excel I would expect the returned table to only show 1-7/8(depending on rounding). I've been playing around further and might be creating a circular reference. Also trying to make other suggestions posted here work having no luck at the minute.
Cheers
ID Name Detail Project Valid Date Created Cost Category BRAG Minimum Saving Most Likely Saving Maximum Saving Confidence 1 Name 1 Detail of Idea 1 1 Yes 01/04/2022 Cost reduction Amber £10,000,000.00 £60,000,000.00 £ 80,000,000.00 10% 2 Name 2 Detail of Idea 2 2 Yes 05/05/2022 Cost reduction Amber £30,000,000.00 £59,000,000.00 £100,000,000.00 30% 3 Name 3 Detail of Idea 3 1 Yes 23/05/2022 Cost reduction Green £40,000,000.00 £50,000,000.00 £60,000,000.00 90% 4 Name 4 Detail of Idea 4 3 Yes 01/04/2022 Cost reduction Amber £26,997,546.00 £40,492,405.00 £53,989,873.00 10% 5 Name 5 Detail of Idea 5 1 Yes 11/05/2022 Cost reduction Amber £12,000,000.00 £24,605,136.00 £35,527,078.00 80% 6 Name 6 Detail of Idea 6 4 Yes 24/05/2022 Cost reduction Red £18,000,000.00 £20,000,000.00 £22,000,000.00 10% 7 Name 7 Detail of Idea 7 4 Yes 24/05/2022 Cost reduction Red £15,000,000.00 £20,000,000.00 £25,000,000.00 25% 8 Name 8 Detail of Idea 8 2 Yes 01/04/2022 Cost reduction Green £5,000,000.00 £15,908,552.00 £22,970,179.00 10% 9 Name 9 Detail of Idea 9 1 Yes 25/04/2022 Cost reduction Green £2,000,000.00 £12,000,000.00 £16,000,000.00 80% 10 Name 10 Detail of Idea 10 5 Yes 24/02/2022 Cost reduction Green £10,000,000.00 £12,000,000.00 £15,000,000.00 10% 11 Name 11 Detail of Idea 11 1 Yes 11/06/2022 Cost reduction Amber £2,000,000.00 £10,000,000.00 £11,000,000.00 10% 12 Name 12 Detail of Idea 12 3 Yes 24/02/2022 Cost reduction Amber £10,000,000.00 £10,000,000.00 £15,000,000.00 40% 13 Name 13 Detail of Idea 13 4 Yes 24/02/2022 Cost reduction Green £10,000,000.00 £10,000,000.00 £15,000,000.00 90% 14 Name 14 Detail of Idea 14 2 Yes 15/11/2021 Cost reduction Amber £8,000,000.00 £9,486,312.00 £13,697,178.00 50%
- KT_Bsmart2gethe
Impactful Individual
Hi BenTen22 ,
If I understand the question correctly, you can add a custom step:
EightyPercent = Table.FirstN( #"SortedRow", Number.RoundUp( Table.RowCount(#"SortedRow") * 0.8 ) ),Regards
KT
v-yalanwu-msftCommunity Support
Hi, BenTen22 ;
You could create a custom column.
= Table.SelectRows(#"Sorted Rows", each [value] >= List.Max(#"Sorted Rows"[value])*0.2)Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZjBbuMwDER/pci5QE1RsqS9tXveLyj6/7+x2STrkcmZBAhyIvw8FGck+fv7Yh/2UTYbb1Z+bdv19/b55/J+2bfr3+fl5/1fRWEVNq9/X48KZxXVrn+/bxVFUA5I4ZDSD0jhkNYOiHOIgeJCyjgoLqSMg1JfSakcsh+Myhn9QDSOqOVgNCEE7Woc4gdkF91qB2TnED8YO2cYmtU5pNkB6RxSoaQLCqZrCCmgDCEFlCEoaNjklAKnTE7BdE0OKfOA2MYpjvm6lvCWwY+b4GDETPjegRG+xxzba9+bMP4yyias75gzU96vAN05Mz4EI/CQEytaiTGWKpAwhVNutjvlWHoGulZeUlxQasyxWFFBcU5xUCqnVExA5ZQlLavQggFoYl1AaZxSMM6NU2yLWZYomLJdUCyGWeoHvNk5ZcYso+NxyjLa0lOWpReF/wen3HbsU5alZ8Qoo9NxirJUkbIsQZaM2YSWFGWpYvH+JtZlCRlh/gbDmHD/0jQT9q9omwn/o20m/L9Ms4kAWGbgrqdsOmbuclLFEgB3NakiZll+BBKzcEiJWZafAc84p3g8k2WxyGUXlJhlWSwGoAoKDrGVUwzL3wQFWhqneMyyrCWeyzIFx4xdLH7Msmcd64ICb3ZOQQB00bB4LssQhP/gkAbKEHOMmJnCLTHMUgXWfnKILxmzCS3LAWMT69JimPGOnMIsv8tifuV+dM2E/W/ePYUZXeBTmOVXWY5MIgHKIuiOsZdhlipSmMWKEi+Y+RlQUzilYQconOIpzdJ7wDUutIDinLKMQBUUHDQqpyy7WRVacGZqnOKgNE7JaZbUYl12oQXrsnNKBWUXFERAFxQkcxdaMGNdzBjuGINTKnaZIVYfhhmcghGbHNJglykgODNNDqlY/P9xljCL+zfBSffM3LN0z0wlC0fYv6aLZi5JYUZf9pxmT0bAVAJg33zIKdqbDzmxYpnnh5pY4eha4ZSa0ixWNOxphVOWu5lzypLMzimOk4ZzimGfqZzi6WyW1MYwS1JilqVVQb+aUBI/mKUKCNk5BN3aOWP5hrlzRokfzHI/4wezrAT275xSIWWIfsVL5pM1GWJN0rksVaRz2RMpU1AeSfbzFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, value = _t, name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"value", Int64.Type}, {"name", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"value", Order.Descending}}), #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [value] >= List.Max(#"Sorted Rows"[value])*0.2) in #"Filtered Rows"
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
