Forum Discussion
Compress rows in a table
Hi
In Excel, I have a vba macro which basically compresses rowsin a table which have same values
Logic is ELR, TID, CODE should match and FM = SM of next row
In Excel, I update the FM with the FM of the next row and delete the next row, then do the same for the next row etc.
Looking to do the same in Query editor or DAX
I have tried a basic SUMMARIZE but MIN (SM) and MAX (FM) isnt going to work as it doesnt account for differences betwen FM and SM of next row and rolls data up too much and itodoesnt take int the Code .. returning 3 rows
ELR table has 5000 rows of data
Table = SUMMARIZE('INM',
'INM'[ELR],
'INM'[TID],
'INM'[Code],
"Start Mileage", MIN('INM'[Start Mileage]),
"Finish Mileage", MAX('INM'[Finish Mileage])
)
Other idea was to use EARLER and compare one row to next but couldnt get that to work
Base data and required result is below (sorry I couldnt paste a table)
4 Replies
Hi Anonymous ,
We can use the following steps to meet your requirement.
1. Create a calculated column.
IsEqualToNextRow = [Finish Mileage] = CALCULATE ( SUM ( 'INM'[Start Mileage] ), 'INM', 'INM'[INDEX] = EARLIER ( 'INM'[INDEX] ) + 1, 'INM'[ELR] = EARLIER ( 'INM'[ELR] ), INM[CODE] = EARLIER ( 'INM'[CODE] ), 'INM'[Track ID] = EARLIER ( 'INM'[Track ID] ) )2. Then we can create a calculate column to get the result.
Group = VAR temp = CALCULATE ( MAX ( 'INM'[INDEX] ), 'INM', NOT ( 'INM'[IsEqualToNextRow] ), 'INM'[INDEX] < EARLIER ( 'INM'[INDEX] ), 'INM'[ELR] = EARLIER ( 'INM'[ELR] ), INM[CODE] = EARLIER ( 'INM'[CODE] ), 'INM'[Track ID] = EARLIER ( 'INM'[Track ID] ) ) RETURN IF ( CALCULATE ( COUNTROWS ( 'INM' ), 'INM'[IsEqualToNextRow], 'INM', 'INM'[INDEX] = EARLIER ( 'INM'[INDEX] ) - 1, 'INM'[ELR] = EARLIER ( 'INM'[ELR] ), INM[CODE] = EARLIER ( 'INM'[CODE] ), 'INM'[Track ID] = EARLIER ( 'INM'[Track ID] ) ) > 0, CALCULATE ( MIN ( 'INM'[INDEX] ), 'INM', 'INM'[INDEX] < EARLIER ( 'INM'[INDEX] ), 'INM'[INDEX] > temp, 'INM'[ELR] = EARLIER ( 'INM'[ELR] ), INM[CODE] = EARLIER ( 'INM'[CODE] ), 'INM'[Track ID] = EARLIER ( 'INM'[Track ID] ) ), [INDEX] )BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 __TableOverall 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 )
