If you are a mechanical engineer or mechanical engineering student, then you can skip past this introduction and the background information and just get right to the solution. I know this from my own experience in earning my Mechanical Engineering degree at Ohio Northern University. I had this professor, a Dr. Jed Marquart, for Thermodynamics and Fluid Mechanics. Nice enough guy, but looking back at it, probably a bit of a sadist. Homework, tests and final exams seemed like 75% of the time spent was on doing linear interpolation calculations. Misery...pure misery.
So, since I've been on this kick of demonstrating how to do some numerical methods and other math stuff in Power BI, I recalled the dozens, if not tens of thousands, of hours I spent as an undergraduate painstakingly doing linear interpolation to solve Thermodynamics and Fluid Mechanics problems. And I figured that perhaps, if I could save just one mechanical engineering student just a few tens of thousands of hours of linear interpolation time that it would be worth writing a blog article on how to do linear interpolation with Power BI. Besides, while we generally deal with known data in things like Power BI, having the ability to do interpolation could come in pretty handy for certain situations as well as "What If" analysis. So, here is the technique, but first a little background.
Interpolation falls into the mathematical field of numerical analysis. Essentially, interpolation is a method of creating new data points within the range of a set of known data points. So, for example, suppose we have a set of data points like the following for some unknown function f(x) (reference the Wikipedia page here😞
x f(x)
0 0
1 0 . 8415
2 0 . 9093
3 0 . 1411
4 −0 . 7568
5 −0 . 9589
6 −0 . 2794
But, what we are interested in is what the value of f(x) is for a value of x=2.5. How can we accomplish this? Well, we can assume that the values between our known values of f(x) approximately fall along a straight line, like so:
Using the two-point equation of a line, we get: y - y1 = m (x - x1), where m is the slope of the line. The slope of a line can be calculated with the formula m = (y2 - y1)/(x2 - x1). Thus, for x3 that is between x1 and x2, we get the final formula:
y3 = y1 + (x3 - x1) (y2 - y1)/(x2 - x1)
Plugging in our values we get:
y3 = .9093 + (2.5 - 2)(.1411-.9093)/(3-2)
y3 = .5252
So, you might be able to see how this could become a tad annoying if you had to do this 57 times in to solve a single Thermodynamics problem...
So, as a practical example of using interpolation, I chose to go grab some Thermodynamic steam tables from this site here. Specifically, this table. Now, to understand this data, these are tables of the properties of water at specific temperatures. These kinds of property tables are very common in Thermodynamics and Fluid Mechanics and come in both chart (diagram) and table form. For example, a Pressure-Enthalpy (P-h) diagram look like:
Nifty. But the problem is that not a single one of Dr. Marquart's problems EVER involved water at a temperature that was actually in these steam tables. Never. Not a single time. But I'm not bitter...
Deep breath. OK, so anyway, the data takes a little clean up, here is my Power Query code for cleaning up and importing the data. You will need to change the path in bold below for your own situation.
let
Source = Excel.Workbook(File.Contents("C:\temp\powerbi\Thermo\H2O_TempSat.xls"), null, true),
Sheet2 = Source{[Name="Sheet1"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",2),
#"Removed Top Rows" = Table.Skip(#"Removed Bottom Rows",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{" Temp", type text}, {" Pressure", type text}, {" volume (m^3/kg)", type text}, {"Column4", type text}, {" energy (kJ/kg)", type text}, {"Column6", type text}, {" enthalpy (kJ/kg)", type text}, {"Column8", type text}, {"Column9", type text}, {" entropy (kJ/kg.K)", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{" Temp", "Temp (C)"}, {" Pressure", "Pressure (MPa)"}, {" volume (m^3/kg)", "volume (m^3/kg) (vf)"}, {"Column4", "volume (m^3/kg) (vg)"}, {" energy (kJ/kg)", "energy (kJ/kg) (uf)"}, {"Column6", "energy (kJ/kg) (ug)"}, {" enthalpy (kJ/kg)", "enthalpy (kJ/kg) (hf)"}, {"Column8", "enthalpy (kJ/kg) (hfg)"}, {"Column9", "enthalpy (kJ/kg) (hg)"}, {" entropy (kJ/kg.K)", "entropy (kJ/kg.K) (sf)"}, {"Column11", "entropy (kJ/kg.K) (sfg)"}, {"Column12", "entropy (kJ/kg.K) (sg)"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column13"}),
#"Removed Top Rows1" = Table.Skip(#"Removed Columns",1),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Top Rows1",{{"Temp (C)", type number}, {"Pressure (MPa)", type number}, {"volume (m^3/kg) (vf)", type number}, {"volume (m^3/kg) (vg)", type number}, {"energy (kJ/kg) (uf)", type number}, {"energy (kJ/kg) (ug)", type number}, {"enthalpy (kJ/kg) (hf)", type number}, {"enthalpy (kJ/kg) (hfg)", type number}, {"enthalpy (kJ/kg) (hg)", type number}, {"entropy (kJ/kg.K) (sf)", type number}, {"entropy (kJ/kg.K) (sfg)", type number}, {"entropy (kJ/kg.K) (sg)", type number}})
in
#"Changed Type2"
OK, so what you should have loaded into a table now are the properties of water at temperatures in increments of 5 degrees Celsius from about 0 degrees to about 373 degrees. I named my table H2OTempSat.
Next, we want to create a table for our Interpolated values. You can do this by going to the Modeling tab of the ribbon and choosing New Parameter from the What If section or just click on New Table and use the following formula:
Interpolation = GENERATESERIES(1,373,1)
You should now have an Interpolation table with numbers from 1 to 373 in increments of 1. Rename the single column in the table to Temp (C).
Note that there is no reason to relate these tables to one another. In fact, I would advise against it, although I have specifically engineered the formula to allow for active relationships between your interpolation table and the lookup table. In any event, create the following measusre:
Pressure (MPa) =
VAR x3 = MAX(Interpolation[Temp (C)])
VAR match = CALCULATE(MAX(H2OTempSat[Pressure (MPa)]),FILTER(ALL(H2OTempSat),[Temp (C)]=x3))
VAR x1 = CALCULATE(MAX(H2OTempSat[Temp (C)]),FILTER(ALL(H2OTempSat),[Temp (C)]<=x3))
VAR x2 = CALCULATE(MIN(H2OTempSat[Temp (C)]),FILTER(ALL(H2OTempSat),[Temp (C)]>=x3))
VAR y1 = CALCULATE(MAX(H2OTempSat[Pressure (MPa)]),FILTER(ALL(H2OTempSat),[Temp (C)]<=x3))
VAR y2 = CALCULATE(MIN(H2OTempSat[Pressure (MPa)]),FILTER(ALL(H2OTempSat),[Temp (C)]>=x3))
RETURN IF(NOT(ISBLANK(match)),match,y1 + (x3 - x1) * (y2 - y1)/(x2 - x1))
So, what this measure is doing is calculating the Pressure (MPa) from our steam table by interpolating between the known values of pressure at the specific temperatures listed in our steam table. Line by line:
Note the use of the ALL function within the FILTER function. This is the reason that we can handle relationships between our interpolation table and our lookup table.
We can now easily create a simple report to show our interpolation results. First, create a Bubble Chart of Temp (C) and Pressure (MPa) from our original table, H2OTempSat. Then, create a second Bubble Chart of Temp (C) and Pressure (MPa) from our Interpolation table.
We should end up with something like the report below:
As one can clearly see, the interpolated values for Pressure (MPa) neatly fill in the gaps from our original table.
Dr. Marquart was a sadist.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.