Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
2 | |
2 | |
1 | |
1 |
User | Count |
---|---|
22 | |
2 | |
2 | |
2 | |
2 |