Power Query M - Optimization - Group and Filter

[vojtechsima]

Hi, guys,
I have perfectly working M code, however, I wonder if there's the possibility to make it faster.

The goal of the Column:
Check Document (ID) and if one of the rows in column Action_Performed is "Connection", then return "YES for each row where the Document is, otherwise "NO".

Example:

Document	Action_Performed	Result
123	Connection	YES
123	Start	YES
123	End	YES
444	Start	NO
444	End	NO
444	Start	NO
444	End	NO
444	Middle	NO
555	Connection	YES

My M code for the column:

 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
    let
        CurrentDocument = [Document],
        FindActionPerfromedOnly = Table.Group(#"Changed Type", {"Document", "Action_Performed"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
        FilterConnection = Table.SelectRows(FindActionPerfromedOnly, each [Action_Performed] = "Connection" and [Count] >= 1)
    in
        if List.Contains(FilterConnection[Document], CurrentDocument) then "YES" else "NO")

M code for whole table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7di9jmRFDIbhe9mYoPxbdowIiQhXBIglQEKDhPb+hY9Gq/U75EStjaoD7zfd5/ix6/PnT6L26YdPP/799vbH71///Pvt068/fPvwl6+//fN1nX96+7JOP//55ctff6wP/vca7s4a7x98qPH+4fca7+dXjVeNV41XDdSIiP/WeP/we43387ca76dXjVeNV41XjVeNV41XjVeNV41XjVeNV41dIzNXjXvvOlXVOnX3OonI2UdV2Ucz3Ud33LJE7IVHMvffIffuSFK1M0n3DqVydipV3anUbKdS951KI3Yqzdyp9N6dSqt2Ku3eqUzOTmUqO5WZ7VTmvlNZxE5lmTuV3btTWdVOZd07lcvZqVxlp3LTnWr2zJ3KI3Yqz9yp/F7ccVVhZe3GkyhnpwqVnSpMd6pw26nm+dypInOnint3qqjaqaJ7p0o5O1Wq7FRpulOl206V4TvVvCU7Vd67U2XVTpXdO9WVs1NdlZ3qmuIFdNupbvhOdTN2qnlbd6pbtVPd7p2q5OxUpbJTlelOVW47VYWjL2TsVHVzp5qusVNV907VcnaqVtmp2nSnaredqsN3qs7Yqfom2lXdnWq6F/rVmX84q6BjHePF8HFDzzrhaFonA13r3ETbOnUL566P7RT5RAX5xBT5xA35JHCJJJLAQeSi0YsUWr1Io9mLHrb76e/s98aGr86Or8GWr8mer5dNX4tdX5tt3w76/nwdaPwPOMhnbgQp0PvFEs1f7KL7ixXav1ij/4sfACCuEGB+HhDwCIh8HkBAPINiXjAgXnBAvAGBxIEEEgoKJAwWzOMCDB6SkS8SHEhceCBRl6R30fQDEiQVJkgaUJB0qDCPL1h4ZgTkywsYJAsySDZokHtgg1w9HDoMOsh18CA34MO8TgDiGVqQ7xaIkNswQuoACSmFElIGJqRcORUFoJBKSDGvN6h4pijkqwYW0gdaSCu4kDZ4Ie0AQzoghnQ6x7YLM6bdAI1nrMPkdg7Y0KNwY9IBjvn2IMf8uqBjnj7YMW8H8Ji3F3pMdwEfz5yJfNMgkU8Mfoyu8GPQhx8zi8CPGZHgx0xu8GMGSvgxc259HHyRTw1+qDr8UA34oZrwQ/XCD9WCH6oNP9QO/Jj/Hn48kzgnc4cfagE/1BJ+qF34oVbwQ63hh/qBH+rCvWHaMfLNaoB8Hh9Wh+Tu4JfLgxe3B2+uD3G4P4TAj/k54MfwCT+eXQX5IuGHxg3uNgU/NBp+aB74oSnwQ9Pgxzwe8GM4hx/P8oR8eeGHZsEPzYYfek9x+xL4odfgh16HH/O4wo8ZL+DHs80h3y34obfhh9aBH1oCP7TscD10+KEV8GNeH/gx4w78eNZL5KuGH9oHfmgL/NA2+KHt8EM7lPtrwo95neHHjF/w49l3scOeAz/sCPywY/Bj0sGP+fbgx/y68GOePvgxbwf8mLcXfjwLOPLNQIt8M0Ainzj8GG3hx0wD8GOmFfgx01TwAqCTNwAHfjw3Asg3DRP5pkEhnwb8mC0RfszyCj9mp4Yfs+rDD5svFPnmD/h4RYF85vDDLOCHWcIPsws/zAp+mDX8MD/wY/47+DE8wI/nzoR3KAE/zBN+mF/4YV7ww7zhh8WBHxYCP+bPhx+zTsCP5xIH+SKNlzwXflgU/LBo+GF54IelwA9L5QXUtF/km/Ed+TJ5B5WXl1BZH26h+sM11OE91BVeRF2FH/M4wI/hG37MugU/nmsu5LsFP+w2/LA68MNKivdkCj+sHH7M4wk/ZpyAH7P+wY/n3g35quGH9YEf1gI/rBV+WPvhRV7Aj3ld4MeMN/Bj1lH48VwE4jbvHPjhR+CHH/3ux6//Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Document = _t, Action_Performed = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document", Int64.Type}, {"Action_Performed", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
    let
        CurrentDocument = [Document],
        FindActionPerfromedOnly = Table.Group(#"Changed Type", {"Document", "Action_Performed"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
        FilterConnection = Table.SelectRows(FindActionPerfromedOnly, each [Action_Performed] = "Connection" and [Count] >= 1)
    in
        if List.Contains(FilterConnection[Document], CurrentDocument) then "YES" else "NO")
in
   #"Added Custom"

Whole PBIX attached.

[yingyinr · Accepted solution]

Hi @vojtechsima ,

I updated your sample pbix file,  please find the details in the attachment. You can update the codes as below in Advanced Editor, later check if it can run faster...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7di9jmRFDIbhe9mYoPxbdowIiQhXBIglQEKDhPb+hY9Gq/U75EStjaoD7zfd5/ix6/PnT6L26YdPP/799vbH71///Pvt068/fPvwl6+//fN1nX96+7JOP//55ctff6wP/vca7s4a7x98qPH+4fca7+dXjVeNV41XDdSIiP/WeP/we43387ca76dXjVeNV41XjVeNV41XjVeNV41XjVeNV41dIzNXjXvvOlXVOnX3OonI2UdV2Ucz3Ud33LJE7IVHMvffIffuSFK1M0n3DqVydipV3anUbKdS951KI3Yqzdyp9N6dSqt2Ku3eqUzOTmUqO5WZ7VTmvlNZxE5lmTuV3btTWdVOZd07lcvZqVxlp3LTnWr2zJ3KI3Yqz9yp/F7ccVVhZe3GkyhnpwqVnSpMd6pw26nm+dypInOnint3qqjaqaJ7p0o5O1Wq7FRpulOl206V4TvVvCU7Vd67U2XVTpXdO9WVs1NdlZ3qmuIFdNupbvhOdTN2qnlbd6pbtVPd7p2q5OxUpbJTlelOVW47VYWjL2TsVHVzp5qusVNV907VcnaqVtmp2nSnaredqsN3qs7Yqfom2lXdnWq6F/rVmX84q6BjHePF8HFDzzrhaFonA13r3ETbOnUL566P7RT5RAX5xBT5xA35JHCJJJLAQeSi0YsUWr1Io9mLHrb76e/s98aGr86Or8GWr8mer5dNX4tdX5tt3w76/nwdaPwPOMhnbgQp0PvFEs1f7KL7ixXav1ij/4sfACCuEGB+HhDwCIh8HkBAPINiXjAgXnBAvAGBxIEEEgoKJAwWzOMCDB6SkS8SHEhceCBRl6R30fQDEiQVJkgaUJB0qDCPL1h4ZgTkywsYJAsySDZokHtgg1w9HDoMOsh18CA34MO8TgDiGVqQ7xaIkNswQuoACSmFElIGJqRcORUFoJBKSDGvN6h4pijkqwYW0gdaSCu4kDZ4Ie0AQzoghnQ6x7YLM6bdAI1nrMPkdg7Y0KNwY9IBjvn2IMf8uqBjnj7YMW8H8Ji3F3pMdwEfz5yJfNMgkU8Mfoyu8GPQhx8zi8CPGZHgx0xu8GMGSvgxc259HHyRTw1+qDr8UA34oZrwQ/XCD9WCH6oNP9QO/Jj/Hn48kzgnc4cfagE/1BJ+qF34oVbwQ63hh/qBH+rCvWHaMfLNaoB8Hh9Wh+Tu4JfLgxe3B2+uD3G4P4TAj/k54MfwCT+eXQX5IuGHxg3uNgU/NBp+aB74oSnwQ9Pgxzwe8GM4hx/P8oR8eeGHZsEPzYYfek9x+xL4odfgh16HH/O4wo8ZL+DHs80h3y34obfhh9aBH1oCP7TscD10+KEV8GNeH/gx4w78eNZL5KuGH9oHfmgL/NA2+KHt8EM7lPtrwo95neHHjF/w49l3scOeAz/sCPywY/Bj0sGP+fbgx/y68GOePvgxbwf8mLcXfjwLOPLNQIt8M0Ainzj8GG3hx0wD8GOmFfgx01TwAqCTNwAHfjw3Asg3DRP5pkEhnwb8mC0RfszyCj9mp4Yfs+rDD5svFPnmD/h4RYF85vDDLOCHWcIPsws/zAp+mDX8MD/wY/47+DE8wI/nzoR3KAE/zBN+mF/4YV7ww7zhh8WBHxYCP+bPhx+zTsCP5xIH+SKNlzwXflgU/LBo+GF54IelwA9L5QXUtF/km/Ed+TJ5B5WXl1BZH26h+sM11OE91BVeRF2FH/M4wI/hG37MugU/nmsu5LsFP+w2/LA68MNKivdkCj+sHH7M4wk/ZpyAH7P+wY/n3g35quGH9YEf1gI/rBV+WPvhRV7Aj3ld4MeMN/Bj1lH48VwE4jbvHPjhR+CHH/3ux6//Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Document = _t, Action_Performed = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document", Int64.Type}, {"Action_Performed", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Document"}, {{"Details", each _, type table [Document=nullable number, Action_Performed=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Result", each if List.Contains([Details][Action_Performed], "Connection") then "YES" else "NO"),
    #"Expanded Details" = Table.ExpandTableColumn(#"Added Custom", "Details", {"Action_Performed"}, {"Action_Performed"})
in
    #"Expanded Details"

In addition, you can create a calculated column as below to get the same result...

Column = 
VAR _count =
    CALCULATE (
        COUNT ( 'SpeedTestTable'[Document] ),
        FILTER (
            'SpeedTestTable',
            'SpeedTestTable'[Document] = EARLIER ( 'SpeedTestTable'[Document] )
                && 'SpeedTestTable'[Action_Performed] = "Connection"
        )
    )
RETURN
    IF ( _count >= 1, "YES", "NO" )

Best Regards

[vojtechsima]

Thank you @yingyinr