Forum Discussion

BenKenobi's avatar
BenKenobi
New Member
3 years ago

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 ...
General Comment
Show and Tell
Tips and Tricks
BA_Pete's avatar
BA_Pete
Super User
3 years ago

 

Or just pre-sort and add the index to the nested table. No buffering or removal of columns required:

 

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 ----->
    sortRows = Table.Sort(chgTypes,{{"Series", Order.Ascending}, {"Value", Order.Ascending}}),
    groupSeries = Table.Group(sortRows, {"Series"}, {{"data", each _, type table [Series=nullable text, Value=nullable number]}}),
    addNestedRank = Table.TransformColumns(groupSeries, {"data", each Table.AddIndexColumn(_, "Rank", 1,1)}),
    expandData = Table.ExpandTableColumn(addNestedRank, "data", {"Value", "Rank"}, {"Value", "Rank"})

in
    expandData

 

 

Start:

 

End:

 

Pete

  • BenKenobi's avatar
    BenKenobi
    New Member
    3 years ago

    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.

    • BA_Pete's avatar
      BA_Pete
      Super User
      3 years ago

       

      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