topic Re: Power Query M - Optimization - Group and Filter in Developer

Power Query M - Optimization - Group and Filter

vojtechsima — Tue, 13 Sep 2022 15:20:44 GMT

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.

Re: Power Query M - Optimization - Group and Filter

Anonymous — Wed, 14 Sep 2022 09:31:35 GMT

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

Re: Power Query M - Optimization - Group and Filter

vojtechsima — Fri, 16 Sep 2022 15:26:02 GMT

Thank you @Anonymous