Calculating max value using power query

Power Query
1

Hi @Melissa,

This thread is a continuation of Dax into power query - #13 by leo_89. I calculated the sum of last 48 half-hour slots using the EpochHH column and named it “Last 24 hrs Raw SAIDI” of a column value. Here is the logic of doing that:

Buffered30mRaw = List.Buffer(Table.Sort( #“Changed Type5”[[EpochHH], [#“30 Mins SAIDI”]],{{“EpochHH”, Order.Ascending}})[#“30 Mins SAIDI”]),

FirstNum = List.Min( List.Buffer(#“Changed Type5”[EpochHH])),
Add24HRaw = Table.AddColumn( #“Changed Type5”, “Last 24 hr Raw SAIDI”, each try List.Sum(List.FirstN( List.Skip( Buffered30mRaw, ([EpochHH] - FirstNum - 47) ), 48 ) ) otherwise null, type number ),

The above formula produced the desired output. Now I want to create a new column that picks the max value of “Last 24 hr Raw SAIDI” column. Here is how I am doing that:

Buffered24hr = List.Buffer(Table.Sort( #“Changed Type5”[[EpochHH], [#“Last 24 hr Raw SAIDI”]],{{“EpochHH”, Order.Ascending}})[#“Last 24 hr Raw SAIDI”]),

FirstNum = List.Min( List.Buffer(#“Changed Type5”[EpochHH])),

Max24HRaw = Table.AddColumn( #“Add24HRawSAIFI”, “Max 24 hr Raw SAIDI”, each try List.Max(List.FirstN( List.Skip( Buffered24hr, ([EpochHH] + FirstNum + 47) ), 48 ) ) otherwise null, type number )

However, the above formula returned the null values. I am not share where am I making the mistake. Any help would be really appreciated:

image
image1443×405 37.7 KB

2

Hi @leo_89,

At first glance you were pretty close to solve this on your own :+1:

I think these are the issues.
First you’ve raised an error because the table doesn’t contain the [#“Last 24 hr Raw SAIDI”] column yet, the error trap - returns the null

image
image1441×324 50.4 KB

You need to replace the red outlined reference by the step name indicated in green, like this.

Buffered24hr = List.Buffer(Table.Sort( Add24HRaw[[EpochHH], [#"Last 24 hr Raw SAIDI"]],{{"EpochHH", Order.Ascending}})[#"Last 24 hr Raw SAIDI"]),

Second you have a plus sign instead of a minus… so I don’t think you will get the desired results once you fix the first issue.

I hope this is helpful

1 Like
3

Maybe a little more indication of where and what happens, you can get it by removing the try … otherwise expression, so you see where the error occurs.

I don’t know your data structure, but the first thing I would check is the data type: since you do some arithmetic operations on the data, try to check if these operations are compatible with the data type.

1 Like
4

@Melissa, thanks for your reply. The reason for the plus sign is that I want to take the max value from the column named [Last 24 hr Raw SAIDI] for the next 48 half-hour time slots. I thought that we use minus sign if I want to choose the max value from last 48 half-hour time slots. Am I mistaken here?

5

It now skips the first numer of rows by the sum of: [EpochHH] + FirstNum + 47
To get the current record’s zero based index, try this instead: [EpochHH] - FirstNum

1 Like
6

@Melissa, you are brilliant ma’am.

1 Like