cancel
Showing results for
Did you mean:
Frequent Visitor

## Finding first, second, third occurence

Hi there.

I have this table of data:

I need to calculate a penalty for each row where the penalty is increased for conscutive rows:

The first row with a value above threshold (Index 1, 5, 10) should get the value 1000.

The second consecutive row (Index 2, 6, 11) should get the value 2000.

The third and following rows (Index 3, 7-8, 12) shoud get the value 5000.

I could do a merge to find the preceding row for each row, see if above threshold is true or false and then know if it is the first case but would prefer a nicer way. Is there one?

Please note that the actual data set has data for every hour the last five years, in total about 44000 rows. My experience is that some DAX-examples that work fine on 20 rows never executes on 44000 rows. I would prefer a solution in Power Query.

Thanks,

Pontus

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Finding first, second, third occurence

This could be another option for you:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MACSlgZKsTrRSkYgASMkAWOQgCmSgAmQaYHEN0U3wgwkYIgkYI5uhAVIwARJwBJsBoIPZqNoMQS71AxZBOxUuEtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Value = _t, Threshold = _t]),

ChangedType = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Value", Int64.Type}, {"Threshold", Int64.Type}}),

Grouping = Table.Group( AddAboveThreshold, {"AboveThreshold"}, {"AllRows", each _, type table}, GroupKind.Local),

ValueMapping = #table( type table [Index3= Number.Type, AddedValue = Number.Type], { {1, 1000}, {2, 2000}, {3, 5000} } ),

fnAddValue = ( tbl as table ) as table =>
let
MergeTables = Table.NestedJoin( AddIndxCol, {"Index2"}, ValueMapping, {"Index3"}, "JoinedTable", JoinKind.LeftOuter ),
ExpandCol = Table.ExpandTableColumn( MergeTables, "JoinedTable", {"AddedValue"} ),
in
Table.SelectColumns(FillDown, {"Index", "Value", "Threshold", "AddedValue"} ),

Grouping,
each
if
[AboveThreshold] = true
then
else
Table.SelectColumns( [AllRows], {"Index", "Value", "Threshold"} ),
type table ),

ModifyTableCell,
),
RemovedOtherColumns = Table.SelectColumns(

ChangedType2 = Table.TransformColumnTypes(
RemovedOtherColumns,
{
{"Index", Int64.Type},
{"Value", type number},
{"Threshold", type number},
}
)
in
ChangedType2```

Proud to be a Datanaut!

3 REPLIES 3
Regular Visitor

## Re: Finding first, second, third occurence

I think, as you mentioned, merging to the preceeding two rows would be the best way.

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MACSlgZKsTrRSkYgASMkAWOQgCmSgAmQaYHEN0U3wgwkYIgkYI5uhAVIwARJwBJsBoIPZqNoMQS71AxZBOxUuEtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Value = _t, Threshold = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Value", Int64.Type}, {"Threshold", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Above Threshold", each if [Value] > [Threshold] then "TRUE" else "FALSE"),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Above Threshold", type logical}}),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index.1", "Previous"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Previous"},#"Renamed Columns",{"Index"},"Renamed Columns",JoinKind.LeftOuter),
#"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns", {"Above Threshold"}, {"Renamed Columns.Above Threshold"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded {0}",{{"Renamed Columns.Above Threshold", "Prev.Above Threshold"}}),
#"Renamed Columns3" = Table.RenameColumns(#"Added Custom1",{{"Benutzerdefiniert", "PrevPrev"}}),
#"Merged Queries1" = Table.NestedJoin(#"Renamed Columns3",{"PrevPrev"},#"Renamed Columns3",{"Index"},"Added Custom1",JoinKind.LeftOuter),
#"Expanded {0}1" = Table.ExpandTableColumn(#"Merged Queries1", "Added Custom1", {"Above Threshold"}, {"Added Custom1.Above Threshold"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded {0}1",{{"Added Custom1.Above Threshold", "prevPrev.Above Threshold"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns2",{{"Index", Order.Ascending}}),
if [Prev.Above Threshold] <> null and [Prev.Above Threshold] then
if [prevPrev.Above Threshold] <> null and [prevPrev.Above Threshold] then
5000
else
2000
else
1000
else
0)
in

regards

florian

Super Contributor

## Re: Finding first, second, third occurence

This could be another option for you:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MACSlgZKsTrRSkYgASMkAWOQgCmSgAmQaYHEN0U3wgwkYIgkYI5uhAVIwARJwBJsBoIPZqNoMQS71AxZBOxUuEtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Value = _t, Threshold = _t]),

ChangedType = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Value", Int64.Type}, {"Threshold", Int64.Type}}),

Grouping = Table.Group( AddAboveThreshold, {"AboveThreshold"}, {"AllRows", each _, type table}, GroupKind.Local),

ValueMapping = #table( type table [Index3= Number.Type, AddedValue = Number.Type], { {1, 1000}, {2, 2000}, {3, 5000} } ),

fnAddValue = ( tbl as table ) as table =>
let
MergeTables = Table.NestedJoin( AddIndxCol, {"Index2"}, ValueMapping, {"Index3"}, "JoinedTable", JoinKind.LeftOuter ),
ExpandCol = Table.ExpandTableColumn( MergeTables, "JoinedTable", {"AddedValue"} ),
in
Table.SelectColumns(FillDown, {"Index", "Value", "Threshold", "AddedValue"} ),

Grouping,
each
if
[AboveThreshold] = true
then
else
Table.SelectColumns( [AllRows], {"Index", "Value", "Threshold"} ),
type table ),

ModifyTableCell,
),
RemovedOtherColumns = Table.SelectColumns(

ChangedType2 = Table.TransformColumnTypes(
RemovedOtherColumns,
{
{"Index", Int64.Type},
{"Value", type number},
{"Threshold", type number},
}
)
in
ChangedType2```

Proud to be a Datanaut!

Highlighted
Frequent Visitor

## Re: Finding first, second, third occurence

Really impressive but I have to take a few hours to try to wrap my head around what it is you've done. I like it a lot!

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!