Power query: bucket column into different slabs tiers

Question

I have a table with Company and profile percentages over the previous year.

I want to create another table to show the company profit percentage shared in the different slabs as like below,

Note: % companies column values are just a mock up value.

Here I want to show how many companies in percentage got profited in each slab. For example, if companies count is 100, then if 10 companie's profit are in 0 to 5, then % Companies should have 10% on the first slab.

Answer 1

Load your top table into powerquery [ data ... from table range [x] columns ]

Method1 Use code similar to below which buckets it into custom range as you defined. You can paste it in using home .. advanced editor ... and change Table1 to reflect the name of your actual table

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Profit %", type number}}),
buckets = #table({"Low", "High"}, {{0,5},{5,10},{10,15},{15,20},{20,25},{25,30},{30,100}}),
#"Added Custom2" = Table.AddColumn(buckets, "Title", each Text.From([Low]) & "% - " & Text.From([High]) & "%"),
#"Added Custom1" = Table.AddColumn(#"Added Custom2", "% Companies", each Table.RowCount(Table.SelectRows(#"Changed Type",(x) => x[#"Profit %"] > [Low] and x[#"Profit %"] <=[High] ))),
#"Transform to percentage" = Table.TransformColumns(#"Added Custom1",{{"% Companies", each _ / Table.RowCount(Source), Percentage.Type}})
in #"Transform to percentage"

Method2 you can bucketize into auto generated 5% ranges. You can paste it in using home .. advanced editor ... and change Table1 to reflect the name of your actual table

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", Int64.Type}, {"Profit %", Int64.Type}}),

// generate list of buckets
    List = Table.FromList(List.Transform(List.Generate(() => 0, each _ < 100, each _ + 5), each Text.From(_)), null, {"Low"}),
    #"Changed Type2" = Table.TransformColumnTypes(List,{{"Low", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "High", each [Low]+5, type number),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Title", each Text.From([Low]) & "% - " & Text.From([High]) & "%"),

// split the original list into the buckets
#"Added Custom1" = Table.AddColumn(#"Added Custom2", "% Companies", each Table.RowCount(Table.SelectRows(#"Changed Type",(x) => x[#"Profit %"] > [Low] and x[#"Profit %"] <=[High] ))), 
#"Transform to percentage" = Table.TransformColumns(#"Added Custom1",{{"% Companies", each _ / Table.RowCount(Source), Percentage.Type}})
in #"Transform to percentage"