Forum Discussion
Compress rows in a table
Anonymous - So, very possible to do. The basic technique is here: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
In your case looks like we can use the mileage as our "index" that defines "previous". I will provide a formula, there is a high likelihood of syntax errors. To get a solution that does not have syntax errors Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Here goes:
Result Table =
VAR __Table =
FILTER(
ADDCOLUMNS(
'Table',
"Mod",MOD(COUNTROWS(FILTER('Table',[ELR]=EARLIER('Table'[ELR]) && [TID] = EARLIER('Table'[TID]) && [Code]=EARLIER('Table'[Code]) && [Start Mileage] <= EARLIER('Table'[Start Mileage]))),2) // 1 for odd rows, 0 for even rows
"Next",MINX(FILTER('Table',[ELR]=EARLIER('Table'[ELR]) && [TID] = EARLIER('Table'[TID]) && [Code]=EARLIER('Table'[Code]) && [Start Mileage] < EARLIER('Table'[Start Mileage])),[Finish Mileage])
),
[Mod] = 1
)
RETURN
__Table
Overall logic, add "Next" column to get the next line's Finish Mileage and create a column that tracks odd or even row. Filter out even rows. Didn't test it because didn't feel like typing data and the solution.
Hi Greg
Many thanks for your reply This kindof works but it's not an even odd data
I have looked at your post and code as just want to add a column rather than create another table
I still cant copy and pase data from Excel to here for some reason so have pasted as csv
I do have an Index column and trying to add a column to paste the Index for the matching rows
Rules are ELR must match previous rowm TrackID must match previous row, Code must match previous row,
Start Mileage should match previous row
Using MINX & FILTER will group Index 10 as part of Index 1-8 and it should be left ungrouped as the previous row has a different Code
Here is my sample data
Sample data
INDEX,ELR,TrackID,StartMileage,FinishMileage,CODE
1,WHL,1900,8.168,9.0029,A
2,WHL,1900,9.0029,9.0103,A
3,WHL,1900,15.0223,15.0602,A
4,WHL,1900,15.0602,15.0643,A
5,WHL,1900,15.0643,15.0965,A
6,WHL,1900,15.0965,15.0994,A
7,WHL,1900,15.0994,15.1016,A
8,WHL,1900,19.0634,19.0763,A
9,WHL,1900,19.0763,19.0787,
10,WHL,1900,19.0787,19.0854,A
11,WHL,1900,36.041,36.0497,A
12.WHL,1900,36.0497,36.0532,A
13,WHL,1900,36.0532,36.0562,A
14,WHL,1900,64.0668,64.0795,B
15,WHL,1900,64.0795,64.0858,B
16,WHL,1900,81.141,81.1508,B
17,WHL,1900,81.1508,81.1591,B
18,WHL,1900,99.0501,99.0765,BResult required
INDEX,ELR,TrackID,StartMileage,FinishMileage,CODE,Gr
1,WHL,1900,8.168,9.0029,A,1
2,WHL,1900,9.0029,9.0103,A,1
3,WHL,1900,15.0223,15.0602,A,3
4,WHL,1900,15.0602,15.0643,A,3
5,WHL,1900,15.0643,15.0965,A,3
6,WHL,1900,15.0965,15.0994,A,3
7,WHL,1900,15.0994,15.1016,A,3
8,WHL,1900,19.0634,19.0763,A,8
9,WHL,1900,19.0763,19.0787,,9
10,WHL,1900,19.0787,19.0854,A,10
11,WHL,1900,36.041,36.0497,A,11
12,WHL,1900,36.0497,36.0532,A,11
13,WHL,1900,36.0532,36.0562,A,11
14,WHL,1900,64.0668,64.0795,B,14
15,WHL,1900,64.0795,64.0858,B,14
16,WHL,1900,81.141,81.1508,B,16
17,WHL,1900,81.1508,81.1591,B,16
18,WHL,1900,99.0501,99.0765,B,18This is my code so far which partially works but only groups 2 rows at a time
Gr =
VAR next = MINX(
FILTER('INM',
[ELR] = EARLIER('INM'[ELR]) &&
[Track ID] = EARLIER('INM'[Track ID]) &&
[CODE]=EARLIER('INM'[CODE]) &&
[Start Mileage] = EARLIER('INM'[Finish Mileage]) ),
'INM'[Index])
RETURN
IF (ISBLANK(next),'INM'[Index],next )