Summation of 25% intervals from data with varying counts

[PhDWoes]

Hello all, 

new data-analysis adventurer here! I am not sure what I want to do is possible with PowerQuery, so I turn to you. 

My data consists of columns responding to different samples, with each row then a measurement of that sample. What I strive to do is calculate 25% sections of each column to get the sum of each section, like shown in the picture below. The order of the measurements is important, and thus cannot be changed. 

My problems are that samples have varying numbers of measurements (10-40 measurements), and not all total measurement numbers are divisible by 4 (e.g. 15 measurements - 3.75 measurements would be 25%.) 

Any tips are appreciated! 

[ronrsnfld]

The issue, as you have noted, is that you cannot always have groups of equal sizes.

Since you want to sum the results from each group, one solution is to

•  Divide the list equally resulting in some remainder
• Allocate the remainder one by one to each of the remaining groups.
  • This will result in groups that are sized as evenly as possible
  • Some will have one more than the equal groups

To do this we

• Transpose the table
• Do the calculations row by row 
• Transpose the table again

(This seems to run faster than an alternative solution of unpivoting, grouping and indexing).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY2xDQAgCAR3+ZpCUaYx7L+GCkjUguCd+WcMNBAkpkDJTTMisJmaROu9jYSpltyGgzx9Un2Nrbs4W05DnhaH58zdwMnfN1Qn", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sample 1" = _t, #"Sample 2" = _t, #"Sample 3" = _t, #"Sample 4" = _t]),

//Next step may not be necessary if blanks are actually nulls in your data
    #"Null Blanks" = Table.ReplaceValue(
        Source,
        "",
        null,
        Replacer.ReplaceValue,
        Table.ColumnNames(Source)),

    #"Demoted Headers" = Table.DemoteHeaders(#"Null Blanks"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),

#"Add Count/Sums" = Table.AddColumn(#"Transposed Table","Count/Sums", (rw)=>
        [a=List.Transform(List.RemoveNulls(List.Skip(Record.FieldValues(rw))), each Number.From(_)),
         N=List.Count(a),
         q=Number.IntegerDivide(N,4),
         r=Number.Mod(N,4),
         
         //compute each quarter size and offset
         Q=List.Generate(
             ()=>[i=0, x=if i<r then q+1 else q, o=0],
             each [i]<4,
             each [i=[i]+1, x=if i < r then q+1 else q, o=[o]+[x]],
             each {[x],[o]}),

         QS = List.Accumulate(
                Q,
                {},
                (s,c)=> s & {List.Sum(List.Range(a,c{1},c{0}))}),
         Res={N} & QS][Res]),
    #"Remove Columns" = Table.SelectColumns(#"Add Count/Sums",{"Column1", "Count/Sums"}),
    #"Extracted Values" = Table.TransformColumns(#"Remove Columns", 
        {"Count/Sums", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Count/Sums", 
                                        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), 
                                        {"Count/Sums.1", "Count/Sums.2", "Count/Sums.3", "Count/Sums.4", "Count/Sums.5"}),

    #"Transposed Table1" = Table.Transpose(#"Split Column by Delimiter"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"Sample 1", Int64.Type}, {"Sample 2", Int64.Type}, {"Sample 3", Int64.Type}, {"Sample 4", Int64.Type}
        }),
    
    #"Add Row Labels" = Table.FromColumns(
        Table.ToColumns(#"Changed Type")
        & {{"Total Measurements", "0-25%","25-50%","50-75%","75-100%"}},
        type table[Sample 1=Int64.Type, Sample 2=Int64.Type, Sample 3=Int64.Type, Sample 4=Int64.Type, Measurements=text]),
        
    #"Reordered Columns" = Table.ReorderColumns(#"Add Row Labels",{"Measurements", "Sample 1", "Sample 2", "Sample 3", "Sample 4"})
in
    #"Reordered Columns"

If you want to have the final result showing as in image in your question, you merely need to stack the two tables at the end of the code.

One way of doing that would be to change the very last line of code (after "in" to:

Table.ReorderColumns(
        Table.Combine({
            Table.TransformColumnTypes(#"Null Blanks", List.Transform(Table.ColumnNames(#"Null Blanks"), each {_, Int64.Type})),
            #"Add Row Labels"}),
        {"Measurements", "Sample 1", "Sample 2", "Sample 3", "Sample 4"})

[v-dineshya]

Hi @PhDWoes ,

Thank you for reaching out to the Microsoft Community Forum.

Please try below steps.

1. Created sample data based on screenshot. Please refer below snap.

2.  Please refer below M code.

let
Source = Excel.Workbook(File.Contents("C:\Users\v-dineshya\Downloads\sample.xlsx"), null, true),
Sheet = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
PromotedHeaders = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true]),
Unpivoted = Table.UnpivotOtherColumns(PromotedHeaders, {}, "Sample", "Value"),
AddIndex = Table.Group(
Unpivoted,
{"Sample"},
{{"Data", each Table.AddIndexColumn(_, "Index", 1, 1)}}
),
Expanded = Table.ExpandTableColumn(AddIndex, "Data", {"Value", "Index"}),
WithCount = Table.AddColumn(Expanded, "Count", each List.Count(
Table.SelectRows(Expanded, (r) => r[Sample] = [Sample])[Value]
)),
AddQuartile = Table.AddColumn(WithCount, "Quartile", each
let
c = [Count],
i = [Index],
q1 = Number.RoundUp(c * 0.25),
q2 = Number.RoundUp(c * 0.5),
q3 = Number.RoundUp(c * 0.75)
in
if i <= q1 then "0–25%"
else if i <= q2 then "25–50%"
else if i <= q3 then "50–75%"
else "75–100%"
),
Grouped = Table.Group(AddQuartile, {"Sample", "Quartile"}, {{"Sum", each List.Sum([Value]), type number}}),
Pivoted = Table.Pivot(Grouped, List.Distinct(Grouped[Sample]), "Sample", "Sum"),
Ordered = Table.AddColumn(Pivoted, "SortOrder",
each if [Quartile] = "0–25%" then 1 else if [Quartile] = "25–50%" then 2 else if [Quartile] = "50–75%" then 3 else 4),
Sorted = Table.Sort(Ordered, {{"SortOrder", Order.Ascending}}),
Final = Table.RemoveColumns(Sorted, {"SortOrder"})
in
Final

Note: I took sample data from my local excel file and imported into Power BI. In the above M code, Please replace my local file path with your local file path.

3. Please refer output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[Omid_Motamedise]

Hi @PhDWoes 

What the value 7 in front of 0to 25% means?

[AlienSx]

let
    fx_sample = (measurements) => [
        no_nulls = List.RemoveNulls(measurements),
        msr_by_04 = List.Combine(List.Transform(no_nulls, (x) => List.Repeat({x / 4}, 4))),
        result = List.Transform(List.Split(msr_by_04, List.Count(no_nulls)), List.Sum)
    ][result],
    Source = Excel.CurrentWorkbook(){[Name="samples"]}[Content],
    cols = List.Transform(Table.ToColumns(Source), fx_sample),
    result = Table.FromColumns({{"0..25%", "25..50%", "50..75%", "75..100%"}} & cols)
in
    result

[grazitti_sapna]

Hi @PhDWoes,

Add a index to your data

Add Column → Index Column → From 1

Then unpivot youe mwasurement columns 

• Select the measurement columns (Sample1, Sample2, …).

• Choose Transform → Unpivot Columns.

No Groupby the columns 

Group by Attribute → All Rows.

For each group, add a Custom Column

use Below M code for grouping

let
CountRows = Table.RowCount([AllData]),
AddSection = Table.AddColumn([AllData], "Section", each Number.RoundUp([Index] * 4 / CountRows))
in
AddSection

Now Groupby by attribute and Section columns 

• Remove unnecessary columns except Attribute, Section, Value.

• Group by Attribute + Section, aggregate Value → Sum.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!