Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Hi @bingbadabom,
Hope this case can help you.
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
189 | |
96 | |
67 | |
63 | |
53 |