Time Duration Curves in PowerQuery - SOLVED

[BenKenobi]

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 "Flow" Duration Curve, "Load" Duration Curve, etc).

After struggling to find guides for how to do this, I've finally found a method I feel is satisfactory for general-purpose that I'd like to share with everyone. Enjoy!

For this example, I'm using this tool to get an hourly CSV

https://re.jrc.ec.europa.eu/pvg_tools/en/#HR

The basic idea is to start with all the time-series in what I like to call a "Vertical" format (i.e. only 3 columns - Series, Time, Value):

You can have other informational columns as desired, but you will need to use "Group By" as desired.

The important part, is to get every time-series in separate tables by using the "Group By" command.

After the Group By, you should have each individual time-series in its' own "Table" object like below.

In this case, I have two (2) time-series which each need a "Time Duration Curves" created.

Next, you can do transformations on each of the Tables (per row) by adding a custom column.

In the below snapshot, I perform 3 actions:

1. Sort the data by Value.
2. Buffer the table. This forces PowerQuery to save the sort-order on the data, as it's important for the next step.
3. Add an index column to the table, which gives you the "Rank" of each value with respect to its' own table.

Table.AddIndexColumn(
    Table.Buffer(
        Table.Sort(
            [Data],
            {{"Value", Order.Ascending}}
        )
    )
,"Rank",0,1)

Next, you can delete the original columns, and expand the Ranked tables.

After expanding it back out, you'll have the data with the "Rank" column, available for plotting in a Duration-Curve.

After loading the data into a spreadsheet, you can create a graphic where the X-Axis is "Rank", and Y-Axis is "Value".

The result will be a Duration-Curve, showing how many samples are below or above certain values.

Anyhow, I hope this helps someone with time-duration curves in PowerQuery.

Enjoy! 

[BA_Pete]

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]

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]

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