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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Grosskopf
New Member

Add Column recalculates on Filter Column - faster option?

I have a query that compares a value in a row to the same value in a previous row. If the value is the same in the previous row the Add Column value is set to 0, if the value is different to the value in the previous row the Add Colum value is set to 1.

 

This works well but it does take a bit of time when comparing thousands of rows.

 

My code for adding the column that is to be filtered:

 

#"Add SpeciesChange" = Table.AddColumn(#"Reorder Columns", "SpeciesChange", each if #"Added Index"{[Index]} [Index] = 0 then "1" else if #"Added Index"{[Index]-1} [Name] = [Name] then "0" else "1")

 

My problem is that the calculation for SpeciesChange values appears to be re-run when I filter the list to show only 1's. The Filter step also takes much longer than the original Add Column calculation.

 

My code for filtering the SpeciesChange list:

 

#"Filtered Rows" = Table.SelectRows(#"Add SpeciesChange", each ([SpeciesChange] = "1"))

 

Is there a better way to do this so that is significantly faster?

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

NewStep1=Table.Buffer(#"Added Index"),

NewStep2=Table.FromRecords(List.Accumulate(Table.ToRecords(#"Reorder Columns"),{},(x,y)=>if NewStep1{y[Index]}[Index]=0 or NewStep1{y[Index]-1}[Name]<>y[Name] then x&{y} else x))

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

NewStep1=Table.Buffer(#"Added Index"),

NewStep2=Table.FromRecords(List.Accumulate(Table.ToRecords(#"Reorder Columns"),{},(x,y)=>if NewStep1{y[Index]}[Index]=0 or NewStep1{y[Index]-1}[Name]<>y[Name] then x&{y} else x))

Thanks Daniel, that was perfect. Quick copy and paste and we're done.

Now I just have to work out what that code means 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors