Compress rows in a table

[amitche3]

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)

[v-zhenbw-msft]

Hi @amitche3 ,

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.

[amitche3]

Many thanks.  Your solution worked perfectly.  When grouped, it gave same result as my vba macro ...  which I now dont need to run every time the data refreshes

[Greg_Deckler]

@amitche3 - 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/3395...

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.

[amitche3]

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,B

Result 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,18

This 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 )