PowerQuery Count calculated rows of big table (Performance Issue)

Question

I'm having a table looking like this

Record	Old.AttributeA	New.AttributeA	AttributeA_IsDifferent
0001	A	A	false
0002	B	C	true
...	...	...	...
5353	A	X	false

The is different column is calculated by doing an equality check on the old and new value.

 Table.AddColumn(source, "AttributeA_IsDifferent", each [Old.AttributeA] <> [New.AttributeA], type logical))

Now I'd like to have an additional pivot table based on the table above that looks like this:

Record	Column	Differences Count
001	AttributeA	3
002	AttributeB	5
...	...	...
135	AttributeZZ	12

For that I currently doing a count of row on the table:

= Table.AddColumn(source, "DifferencesCount", each  Table.RowCount(Table.SelectRows(ReportTable, (x)=> Record.Field(x, GetColumnName_IsDifferent2([Column])) = true)))

The GetColumnName_IsDifferent2([Column]) gets the correct column name based on the ColumnName in the "Column" column.

Problem is that this count is freaking slow. It takes about 1 second per attribute. Approx. 2 Minutes for the whole pivot table. Is there any room for improving the duration?

Answer 1

You didnt accept or comment on any answers in your other question PowerQuery: Adding multiple columns with calculation . What is the motivation for anyone to answer this one?

That said, lets give it one more go. Try an unpivot followed by a group

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Record"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each Text.Contains([Attribute], "IsDifferent")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","_IsDifferent","",Replacer.ReplaceText,{"Attribute"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Value", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Record", "Attribute"}, {{"Differences Count", each List.Sum([Value]), type nullable number}})
in  #"Grouped Rows"