Adding Age Banding to your Power BI Desktop

Topics: Power BI, Big data , M Language

Posted By: Nik - Shahriar Nikkhah January 24, 2016

You may have an age banding dimension (DimAgeBand) in your BI world, your biggest challenge will be adding new age band to the current dimension dynamically with the minimum amount of changes.

Age Banding Overview

Grouping an age range is basically called "Age Banding", every business or analysts looks at the age banding dimension form a different angle (or point of view), they like to analyze the age in different groups or [AgeBand].

In your DimAgeBand-ing you will have an [Age] or [AgeID] field that will play the role of your unique key and other fields that represents the business requirement like [AgeBand1] and [AgeBand2].

Top 20 and bottom 20 records.

The Code In M Language

Use the mentioned M Language code in a "Blank Query" in Power BI to define the business age band.


--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

let
    // Make an Age Range, Max number is 127 records else if needed more records you must change the data type from Int8 to Int16
    Source = {-1.. 127},
    // Convert List to table
    ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    // Rename field to [Age]
    RenamedColumns = Table.RenameColumns(ConvertedToTable,{{"Column1", "Age"}}),
    // Change the data type of the field [Age] to Int8 (-127 ... +127)
    ChangeDataType= Table.TransformColumnTypes(RenamedColumns ,{{"Age", Int8.Type}}),
    // Add AgeBand1
    AddedAgeBand1Column = Table.AddColumn(ChangeDataType, "AgeBand1", each
  if   -1 = [Age] then "Unknown"
  else if   0 <= [Age] and [Age] <   18 then "Under 18"
  else if  18 <= [Age] and [Age] <=  24 then "[18-24]"
  else if  25 <= [Age] and [Age] <=  34 then "[25-34]"
  else if  35 <= [Age] and [Age] <=  44 then "[35-44]"
  else if  45 <= [Age] and [Age] <=  54 then "[45-54]"
  else if  55 <= [Age] and [Age] <=  64 then "[55-64]"
  else if  65 <= [Age] and [Age] <=  74 then "[65-74]"
  else if  75 <= [Age] and [Age] <=  84 then "[75-84]"
  else if  85 <= [Age] and [Age] <=  94 then "[85-94]"
  else if  95 <= [Age] and [Age] <= 104 then "[95-104]"
  else if 105 <= [Age] and [Age] <= 114 then "[105-114]"
  else if 115 <= [Age] and [Age] <= 124 then "[115-124]"
  else "[125 +"),
    // Add AgeBand2
    AddedAgeBand2Column = Table.AddColumn(AddedAgeBand1Column, "AgeBand2", each
  if   -1 = [Age] then "Unknown"
  else if   0 <= [Age] and [Age] <   20 then "Under 20"
  else if  20 <= [Age] and [Age] <=  29 then "[20-29]"
  else if  30 <= [Age] and [Age] <=  39 then "[30-39]"
  else if  40 <= [Age] and [Age] <=  49 then "[40-49]"
  else if  50 <= [Age] and [Age] <=  59 then "[50-59]"
  else if  60 <= [Age] and [Age] <=  69 then "[60-69]"
  else if  70 <= [Age] and [Age] <=  79 then "[70-79]"
  else if  80 <= [Age] and [Age] <=  89 then "[80-89]"
  else if  90 <= [Age] and [Age] <=  99 then "[90-99]"
  else if 100 <= [Age] and [Age] <= 109 then "[100-109]"
  else if 110 <= [Age] and [Age] <= 119 then "[110-119]"
  else "[120 +")
in
    AddedAgeBand2Column
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

What was so Dynamic?

In the above M code you can add new age banding [AgeBand3] as required by the business, without changing anything in your Power BI.

Reference

https://powerbi.microsoft.com/en-us/