I previously got help creating a query for calculating a weighted average for missing values in my table in this post.
My table has 550 000 rows and the query has been running since tuesday on my I7-7700K and is not finished yet.
With only the top 1000 rows the query finishes in about 6-7 minutes.
Is there a way to speed up this query? As i get it, for every row the entire table is filtered and sorted.
This is from the example data created in my previous post (just for reference)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lHyqAAShgYgpquhUqwOTLgSSBhBhI3AwpYIxQi1cEEjdJUg/cZgQWOwoAVcuylCO1zQyBSh3QKu3RSqPRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Analysis = _t, Quality = _t, Value = _t, Element = _t]),
AnalysisAsInt = Table.TransformColumnTypes(Source,{{"Analysis", Int64.Type}}),
LastTwoAnalysis = Table.AddColumn(AnalysisAsInt, "LastTwoAnalysisTable",
(parentRow as record) =>
Table.FirstN(
Table.Sort(
Table.SelectRows(AnalysisAsInt,
(childRow as record) =>
childRow[Quality] = parentRow[Quality]
and
childRow[Element] = parentRow[Element]
and
childRow[Analysis] < parentRow[Analysis]
),
{{"Analysis", Order.Descending}}
),
2
)
)
in
LastTwoAnalysis
