Forum Discussion
Time Duration Curves in PowerQuery - SOLVED
Hi everyone, As a power systems engineer, I frequently have to take time-stampled data sources and look at both the time-series, as well as what's called a "Time Duration Curve" of the data (aka ...
Hi BA_Pete,
FYI, I gave your method a try on a larger dataset, and it doesn't keep the sort order when performing the grouping. Adding a "Table.Buffer(sortRows)" fixes the issue.
Hm, interesting. I've never had an issue with that before, but I usually work on relatively small data sets.
I'll have to test myself and see if there's some kind of threshold where the sorting gets lost. Maybe it's if paging has to occur due to Group By being a whole-table operation?
Maybe try this. I've done the sort on the nested table instead:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCktNTyxW0lEyNFKK1UFwjUxRuIYWYG5wYp5bUWIeUMAMjW9siiZgZKAUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Series = _t, Value = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Series", type text}, {"Value", Int64.Type}}),
// Relevant steps ----->
groupSeries = Table.Group(chgTypes, {"Series"}, {{"data", each _, type table [Series=nullable text, Value=nullable number]}}),
sortNestedRows = Table.TransformColumns(groupSeries, {"data", each Table.Sort(_, {"Series", "Value"})}),
addNestedRank = Table.TransformColumns(sortNestedRows, {"data", each Table.AddIndexColumn(_, "Rank", 1,1)}),
expandData = Table.ExpandTableColumn(addNestedRank, "data", {"Value", "Rank"}, {"Value", "Rank"})
in
expandData
Pete