Forum Discussion
Linear regression in Power Query with grouping
Hello I followed Stephen Roughley's brilliant post: https://stephenroughley.com/2022/02/28/simple-linear-regression-in-power-query/ and it almost worked for me. My problem is I have my xcol repeat...
Here's an example similar to yours that demonstrates what I was suggesting:
let Forecast = (sourceTable as table, xcol as text, ycol as text) => let range = Table.SelectRows(sourceTable, each Record.Field(_, ycol) <> 0), rowcount = Table.RowCount(range), sumx = List.Sum(Table.Column(range, xcol)), sumx2 = List.Sum(List.Transform(Table.Column(range, xcol), each Number.Power(_, 2))), sumy = List.Sum(Table.Column(range, ycol)), sumxy = List.Sum(Table.TransformRows(range, each Record.Field(_, xcol) * Record.Field(_, ycol))), avgx = List.Average(Table.Column(range, xcol)), avgy = List.Average(Table.Column(range, ycol)), Slope = ((rowcount * sumxy - sumx * sumy) / (rowcount * sumx2 - Number.Power(sumx, 2))), Intercept = avgy - Slope * avgx, Result = Table.AddColumn(sourceTable, Text.Combine({ycol, "Forecast"}), each if Record.Field(_, ycol) = 0 then Intercept + Slope * Record.Field(_, xcol) else 0, type number ) in Result, Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZBRCsAgDEPv0m8/pkbHziJ+bPc/xIw4XOhHCTxC0rY1ixbsHkNNxXrYKHGioEznKQhjDiHFkSrkWXUoglgHdbEuqwsuatel3y1VCDWqaZ4CQXBJmv0tfimiVkVzcUVwUT58/am/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dimA = _t, dimB = _t, dimX = _t, FactY = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"dimA", Int64.Type}, {"dimB", type text}, {"dimX", Int64.Type}, {"FactY", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"dimA", "dimB"}, {{"SubTable", each Forecast(_, "dimX", "FactY"), type table [dimA=nullable number, dimB=nullable text, dimX=nullable number, FactY=nullable number, FactYForecast=nullable number]}}), #"Expanded SubTable" = Table.ExpandTableColumn(#"Grouped Rows", "SubTable", {"dimX", "FactY", "FactYForecast"}, {"dimX", "FactY", "FactYForecast"}) in #"Expanded SubTable"The highlighted column is the result:
Here's an example similar to yours that demonstrates what I was suggesting:
let
Forecast = (sourceTable as table, xcol as text, ycol as text) =>
let
range = Table.SelectRows(sourceTable, each Record.Field(_, ycol) <> 0),
rowcount = Table.RowCount(range),
sumx = List.Sum(Table.Column(range, xcol)),
sumx2 = List.Sum(List.Transform(Table.Column(range, xcol), each Number.Power(_, 2))),
sumy = List.Sum(Table.Column(range, ycol)),
sumxy = List.Sum(Table.TransformRows(range, each Record.Field(_, xcol) * Record.Field(_, ycol))),
avgx = List.Average(Table.Column(range, xcol)),
avgy = List.Average(Table.Column(range, ycol)),
Slope = ((rowcount * sumxy - sumx * sumy) / (rowcount * sumx2 - Number.Power(sumx, 2))),
Intercept = avgy - Slope * avgx,
Result = Table.AddColumn(sourceTable, Text.Combine({ycol, "Forecast"}), each
if Record.Field(_, ycol) = 0
then Intercept + Slope * Record.Field(_, xcol)
else 0, type number )
in
Result,
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZBRCsAgDEPv0m8/pkbHziJ+bPc/xIw4XOhHCTxC0rY1ixbsHkNNxXrYKHGioEznKQhjDiHFkSrkWXUoglgHdbEuqwsuatel3y1VCDWqaZ4CQXBJmv0tfimiVkVzcUVwUT58/am/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dimA = _t, dimB = _t, dimX = _t, FactY = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"dimA", Int64.Type}, {"dimB", type text}, {"dimX", Int64.Type}, {"FactY", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"dimA", "dimB"}, {{"SubTable", each Forecast(_, "dimX", "FactY"), type table [dimA=nullable number, dimB=nullable text, dimX=nullable number, FactY=nullable number, FactYForecast=nullable number]}}),
#"Expanded SubTable" = Table.ExpandTableColumn(#"Grouped Rows", "SubTable", {"dimX", "FactY", "FactYForecast"}, {"dimX", "FactY", "FactYForecast"})
in
#"Expanded SubTable"
The highlighted column is the result:
Thank you so much for the time.
This works, but it is way too complicated for me. I need to understand my code.
I have come to realize that the problem has nothing to do with the linear regression, but rather to figure out how I can reference the exact rows I need for the calculation. I will post a new question for that.
At a high level, this is fairly straightforward. I defined a function that adds a column to a table and then instead of applying that function to the whole data table at once, I applied it to a bunch of subtables (determined by the dimension grouping) separately. That's why I referenced my answer here. It's pretty much the same question except with a more complicated function than Table.AddIndexColumn.