Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
amitche3
Frequent Visitor

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)

 

 

INM ELR TID Maintenance Responsibility.jpg

 

 

4 REPLIES 4
v-zhenbw-msft
Community Support
Community Support

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

 

Com 2.jpg

 

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

 

Com 1.jpg

 

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.

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
Super User
Super User

@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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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 )

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.