excelpowerquery

Power Query Getting the percentage distribution for each column in a table, dynamic columns

I'm practically new in Power Query, I did some simple queries in the past, but I'm trying to make a dynamic one to process the data in a table to calculate the percentage distribution of the absolutes and this is what I have so far...

Basically I'm stuck in getting the final step (and the most important one), the division of the recorded value times the total of the column,

My final result should be the percentage distribution and my example data is something like this, my interest on making it dynamic is for always to be able to detect the columns, since this are suppose to be dynamic (always increasing in terms of periods),

Product Q1 - 2020 Q2 - 2020 Q3 - 2020 Q4 - 2020 Q1 - 2021 Q2 - 2021 Q3 - 2021 Q4 - 2021
P1 1144 2737 3545 3696 4279 4249 4167 4124
P2 157 161 166 163 166 174 163 175
P3 257 388 485 529 553 566 585 598
P4 251 461 587 709 820 848 878 924
P5 262 402 493 465 502 512 507 550
P6 193 220 236 255 256 269 251 253
P7 160 161 171 184 214 236 243 244
P8 177 249 300 384 488 518 558 566
P9 202 226 231 245 246 258 283 275
P10 154 154 154 154 154 157 166 163
P11 155 156 167 166 203 236 251 246
P12 165 163 159 168 174 167 170 177
P13 156 159 157 160 170 161 164 169
P1 33.3% 48.6% 51.7% 50.8% 52.0% 50.9% 49.7% 48.7%
P2 4.6% 2.9% 2.4% 2.2% 2.0% 2.1% 1.9% 2.1%
P3 7.5% 6.9% 7.1% 7.3% 6.7% 6.8% 7.0% 7.1%
P4 7.3% 8.2% 8.6% 9.7% 10.0% 10.2% 10.5% 10.9%
P5 7.6% 7.1% 7.2% 6.4% 6.1% 6.1% 6.0% 6.5%
P6 5.6% 3.9% 3.4% 3.5% 3.1% 3.2% 3.0% 3.0%
P7 4.7% 2.9% 2.5% 2.5% 2.6% 2.8% 2.9% 2.9%
P8 5.2% 4.4% 4.4% 5.3% 5.9% 6.2% 6.7% 6.7%
P9 5.9% 4.0% 3.4% 3.4% 3.0% 3.1% 3.4% 3.2%
P10 4.5% 2.7% 2.2% 2.1% 1.9% 1.9% 2.0% 1.9%
P11 4.5% 2.8% 2.4% 2.3% 2.5% 2.8% 3.0% 2.9%
P12 4.8% 2.9% 2.3% 2.3% 2.1% 2.0% 2.0% 2.1%
P13 4.5% 2.8% 2.3% 2.2% 2.1% 1.9% 2.0% 2.0% 
let
        Source = Excel.CurrentWorkbook(){[Name="Line1_Abs"]}[Content],
        //Organization will always be of type text.  The others will be should be numbers, unless user error
        #"Changed Type" = Table.TransformColumnTypes(Source, {{"Product_Regimen", type text}}),
       //function to replace all values in all columns with percentages values
       MultiplyReplace = (DataTable as table, DataTableColumns as list) =>
         let
            Counter = Table.ColumnCount(DataTable),
            ReplaceCol = (DataTableTemp, i) =>
                let
                    colName = DataTableColumns{i},
                    colTotal = List.Sum(Record.FieldValues(_, colName)),
                    //Line not doing the trick
                    ReplaceTable = Table.ReplaceValue(DataTableTemp,each Record.Field(_, colName), each if Record.Field(_, colName) is number then Record.Field(_, colName)/List.Sum(Record.FieldValues(_, colName)) else Record.Field(_, colName),Replacer.ReplaceValue,{colName})
    
                in
                    if i = Counter-1 then ReplaceTable else @ReplaceCol(ReplaceTable, i+1)
         in
            ReplaceCol(DataTable, 0),
        allColumns = Table.ColumnNames(#"Changed Type"),
        #"Multiplied Numerics" = MultiplyReplace(#"Changed Type", allColumns)
    
    in
        #"Multiplied Numerics"
Solution

  • This divides every row by the sum of the column for all columns except those purposely excluded

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ignore={"Product"},
ChosenNames=List.Difference(Table.ColumnNames(Source),Ignore),
ChosenColumns = Table.ToColumns(Table.SelectColumns(Source,ChosenNames)),
Process = Table.FromColumns(Table.ToColumns(Table.SelectColumns(Source,Ignore))&List.Transform(ChosenColumns,(x)=> List.Transform(x, each _/List.Sum(x))),Table.ColumnNames(Source))
in Process

    enter image description here