Home »  Data Normalization

 Data Normalization

Solving  Data Normalization challenge by Power Query, Power BI, Excel, Python and R

In every decision-making process, the first step is to normalize the data. For this task, simply divide each value by its column’s total sum. !!! Just use a single formula!!! The highlighted cell shows the result of dividing 85 by 253.

📌 Challenge Details and Links
Challenge Number: 12
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of  Data Normalization with Power Query

Power Query solution 1 for  Data Normalization, proposed by Omid Motamedisedeh: 
Table.FromColumns(
  List.Transform(Table.ToColumns(Source), each List.Transform(_, (x) => x / List.Sum(_)))
)
Power Query solution 2 for  Data Normalization, proposed by Brian Julius: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), 
  UnpivotOther = Table.UnpivotOtherColumns(AddIndex, {"Index"}, "Attribute", "Value"), 
  Group = Table.Group(
    UnpivotOther, 
    {"Attribute"}, 
    {
      {"All", each _, type table [Index = number, Attribute = text, Value = number]}, 
      {"ColSum", each List.Sum([Value]), type number}
    }
  ), 
  Expand = Table.ExpandTableColumn(Group, "All", {"Index", "Value"}, {"Index", "Value"}), 
  Division = Table.AddColumn(Expand, "Division", each [Value] / [ColSum], type number), 
  Percentage = Table.TransformColumnTypes(Division, {{"Division", Percentage.Type}}), 
  RemoveCols = Table.RemoveColumns(Percentage, {"Value", "ColSum"}), 
  Pivot = Table.Pivot(RemoveCols, List.Distinct(RemoveCols[Attribute]), "Attribute", "Division"), 
  RemoveIndex = Table.RemoveColumns(Pivot, {"Index"})
in
  RemoveIndex
Power Query solution 3 for  Data Normalization, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  res = Table.FromColumns(
    List.Transform(Table.ToColumns(Fonte), (x) => List.Transform(x, each _ / List.Sum(x)))
  )
in
  res
Power Query solution 4 for  Data Normalization, proposed by Ramiro Ayala Chávez: 
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = Table.ToColumns(S),
b = List.Transform(List.Transform(a,List.Sum), each List.Repeat({_},Table.RowCount(S))),
c = Table.FromColumns({List.Combine(a),List.Combine(b)}),
d = Table.AddColumn(c,"D", each Number.Round([Column1]/[Column2],2))[[D]],
e = Table.Split(d,Table.RowCount(S)),
Sol = Table.FromColumns(List.Transform(e, each [D]))
in
Sol
Power Query solution 5 for  Data Normalization, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Col = Table.ToColumns(Source), 
  Sum = List.Transform(Col, List.Sum), 
  Div = List.Transform(
    {0 .. List.Count(Col) - 1}, 
    each List.Transform(Col{_}, (x) => Number.ToText(x / Sum{_}, "##%"))
  ), 
  Sol = Table.FromColumns(Div)
in
  Sol
Power Query solution 6 for  Data Normalization, proposed by John Jairo Vergara Domínguez: 
let
 S = Excel.CurrentWorkbook(){[Name="q"]}[Content],
 T = List.Transform,
 R = T(Table.ToColumns(S), (x) => T(x, each _ / List.Sum(x)))
in
 Table.FromColumns(R)

Blessings!
Power Query solution 7 for  Data Normalization, proposed by Mahmoud Bani Asadi: 
Table.FromRows(
  List.Transform(
    Table.ToRows(Source), 
    each List.Transform(
      List.Zip({_, List.Transform(Table.ToColumns(Source), List.Sum)}), 
      each _{0} / _{1}
    )
  )
)
Power Query solution 8 for  Data Normalization, proposed by Glyn Willis: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"Column1", Int64.Type}, 
      {"Column2", Int64.Type}, 
      {"Column3", Int64.Type}, 
      {"Column4", Int64.Type}, 
      {"Column5", Int64.Type}, 
      {"Column6", Int64.Type}
    }
  ), 
  #"Added Custom" = Table.FromRecords(
    Table.AddColumn(
      #"Changed Type", 
      "Custom", 
      each [
        Col = Table.ColumnNames(#"Changed Type"), 
        rec = List.Buffer(Record.ToList(_)), 
        tr = List.Transform(
          {0 .. List.Count(rec) - 1}, 
          (x) => rec{x} / List.Sum(Table.Column(#"Changed Type", Col{x}))
        ), 
        res = Record.FromList(tr, Col)
      ][res], 
      type record
    )[Custom]
  ), 
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Added Custom", 
    List.Transform(Table.ColumnNames(#"Changed Type"), each {_, Percentage.Type})
  )
in
  #"Changed Type1"

Solving the challenge of  Data Normalization with Excel

Excel solution 1 for  Data Normalization, proposed by محمد حلمي: 
=B3:G8/MMULT(
    SEQUENCE(
        ,
        6
    )^0,
    B3:G8
)
Excel solution 2 for  Data Normalization, proposed by Julian Poeltl: 
=B3:G8/(BYCOL(
    B3:G8,
    LAMBDA(
        ARR,
        SUM(
            ARR
        )
    )
)*SEQUENCE(
    6,
    6
)/SEQUENCE(
    6,
    6
))
Excel solution 3 for  Data Normalization, proposed by Kris Jaganah: 
=B3:G8/BYCOL(
    B3:G8,
    SUM
)
Excel solution 4 for  Data Normalization, proposed by John Jairo Vergara Domínguez: 
=B3:G8/BYCOL(
    B3:G8,
    SUM
)
Excel solution 5 for  Data Normalization, proposed by Mahmoud Bani Asadi: 
=ArrayFormula(
    B3:G8/ mmult(
        ArrayFormula(
            B3:G3^0
        ),
        B3:G8
    )
)
Excel solution 6 for  Data Normalization, proposed by Sunny Baggu: 
=B3:G8/MMULT(
    MAKEARRAY(
        1,
        COLUMNS(
            B3:G3
        ),
        LAMBDA(
            r,
            c,
            r
        )
    ),
    B3:G8
)
Excel solution 7 for  Data Normalization, proposed by Sunny Baggu: 
=B3:G8*100/BYCOL(
    B3:G8,
    LAMBDA(
        a,
        SUM(
            a
        )
    )
)
Excel solution 8 for  Data Normalization, proposed by ALEJANDRO VILLAMIL ARENGAS: 
=B3/SUM(
    B$3:B$8
)
Excel solution 9 for  Data Normalization, proposed by Andy Heybruch: 
=LET(
    data,
    B3:G8,    col,
    BYCOL(
        data,
        LAMBDA(
            a,
            SUM(
                a
            )
        )
    ),    data/col
)
Excel solution 10 for  Data Normalization, proposed by Charles Roldan: 
=LET(
 Sum,
     LAMBDA(
         x,
          SUM(
              x
          )
     ), Divide,
     LAMBDA(
         a,
          b,
          a / b
     ), Id,
     LAMBDA(
         x,
          x
     ), bC,
     LAMBDA(
         f,
          LAMBDA(
              x,
               BYCOL(
                   x,
                    f
               )
          )
     ), Φ,
     LAMBDA(
         f,
          g,
          h,
          LAMBDA(
              x,
               f(
                   g(
              x
          ),
                    h(
              x
          )
               )
          )
     ), Φ(Divide,
     Id,
     bC(
         Sum
     ))
)(B3:G8)
Excel solution 11 for  Data Normalization, proposed by Daniel Madhadha: 
=B3:G8/BYCOL(
    B3:G8,
    LAMBDA(
        mycol,
        SUM(
            mycol
        )
    )
)
Excel solution 12 for  Data Normalization, proposed by Fábio Gatti: 
=LET(
    arr,
    B3:G8,
    arr/BYCOL(
        arr,
        LAMBDA(
            x,
            SUM(
                x
            )
        )
    )
)
Excel solution 13 for  Data Normalization, proposed by Hussein SATOUR: 
=B3:G8/BYCOL(
    B3:G8,
     SUM
)
Excel solution 14 for  Data Normalization, proposed by Nicolas Micot: 
=B3/SOMME(
    B$3:B$8
)
Excel solution 15 for  Data Normalization, proposed by Pieter de B.: 
=B3:G8/MMULT(
    {1,
    1,
    1,
    1,
    1,
    1},
    B3:G8
)
Excel solution 16 for  Data Normalization, proposed by Rick Rothstein: 
=B3:G8/BYCOL(
    B3:G8,
    LAMBDA(
        c,
        SUM(
            c
        )
    )
)
Excel solution 17 for  Data Normalization, proposed by Surendra Reddy: 
=B3:G8/BYROW(
    B3:G8,
    SUM
)
Excel solution 18 for  Data Normalization, proposed by Thang Van: 
=LET(a,
    B3:G8,
    TEXT(a/(BYCOL(
        a,
        LAMBDA(
            col,
            SUM(
                col
            )
        )
    )),
    "#%"))

Solving the challenge of  Data Normalization with R

R solution 1 for  Data Normalization, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)

input = read_excel("files/CH-012.xlsx", range = "B3:G8", col_names = F)
test = read_excel("files/CH-012.xlsx", range = "O3:T8", col_names = F)

result = input %>%
 mutate(across(everything(), ~ .x / sum(.x)))

Solving the challenge of  Data Normalization with Google Sheets

Google Sheets solution 1 for  Data Normalization, proposed by Mahmoud Bani Asadi: 
Google Sheet formula:
=ArrayFormula(B3:G8/BYCOL(B3:G8,Lambda(x,sum(x))))

Leave a Reply