Averages in Feet and Inches

[Anonymous]

Hey all, 

I currently am pulling in data from a web page that I am trying to create a measure for average of a category. Its current format is a text (Ex: 10' 8"). Any suggestions on how to make it a number that can produce an average since it is not possible to compute an average of text. Thanks!

[danextian · Accepted solution]

Hi @Anonymous 

In Power Query, you can extract the feet and inches components of that text column using Extract feature under Add Column tab.

For Feet, that is Text Before Delimter where your delimeter is ' and for inches that is Text Between Delimeters where the first delimeter is a space and the second delimter is ". You will then need to change the data type of these newly created columns to numbers.
Alternatively you can use DAX to extract a string and convert them to numbers. Here are sample formulas.

Foot = 
VAR _singlequote =
    FIND ( "'", 'Table'[Column] ) - 1
RETURN
    VALUE ( LEFT ( 'Table'[Column], _singlequote ) )

Inch = 
VAR _singlequote =
    FIND ( " ", 'Table'[Column] ) + 1
VAR _doublequote =
    FIND ( """", 'Table'[Column] )
VAR __lenght = _doublequote - _singlequote
RETURN
    VALUE ( MID ( 'Table'[Column], _singlequote, __lenght ) )

  Here's a sample pbix for your reference: https://drive.google.com/file/d/1xoHmnqO8ieKHCkPb_lcEg2AwKQj0hiCg/view?usp=sharing 
You can then use these new columns in your aggregation (sum, average, etc)