Forum Discussion
M formula that references other rows in same query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZRBb9pAEIX/yohzDsZAQo6BqG1IaFFAQSjKYbAn9oj1Ll2vaZ1f37WNgw22ox7Q4MO3b+fNm3197T0KYtm76s1RpwgL9naSUvu9RkMaFkoo+wHHn+v0r/PiOr23qxP8jEFCAu6Vxg/OgOUfjmzZnLjbJu6Roy1pEinc81ZgzI3osCjjGjpPNWMEvw5sTMd1+0Vxa+wqEQhzb0+4axJ03KIMcuiJIpVBMxVKmKhUoPTt5wRj2qIQR2pTIIU7fadGrpS01m4ozMTUu6lgULZ29KiKvfABJdxplKZNrz8qyk0NXIYWQYIJaz+mHGmUdQpjnTo98+CZAz//3yw6ro6yxO4OpFNYGgyCr/RG9dsa2tsLw0OE3Co5LMNXk5QYG/QYYWoBkm26bhECtz7PBUbWG5sDO08SW0yi1qkOy+FeuMwwQW0E/V/LC534JD07IYHerjUV/bL3Kmt3TJkQbRJVRPqLRJ3NaIbe70SkEqaaMFathtWDsdxrlkEuLSUZY+1W+z35AZ2W5+f5A3HM8ie7RmE0Jj58x2gr8i1sWdly+0b1AyZql68s7TpUj/1+QjPSnPcboo1n3PFOuOU+ZfyaYvPOJPx8zKTtIbCyk6L8qbl8L8blvM7hOf7lKPNLZvLU0fRNedL5EauQEH5kIWuUvq0+OFVuzcJHeCHtc4fsoJrsKv6EH6gVfCPhhU3CpWGDC3Iaao7tYszsfukiZN1NZ769/QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [School = _t, Player = _t, Sport = _t, #"School has Pool?" = _t, #"School Has Baseball Field?" = _t, #"Year Athlete Started on Team" = _t, #"Year Athlete Left Team" = _t]), #"Grouped Rows" = Table.Group(Source, {"School"}, {{"Gr", each _, type table }, {"Pool Info", each List.Max([#"School has Pool?"]), type nullable text}, {"Baseball Fielad Info", each List.Max([#"School Has Baseball Field?"]), type nullable text}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"School"}), #"Expanded Gr" = Table.ExpandTableColumn(#"Removed Columns", "Gr", Table.ColumnNames ( #"Removed Columns"[Gr]{0})) in #"Expanded Gr"
Solution SageYou can achieve that pretty easy in M with 2 steps.
1. Add conditional Column.
2. Fill Down.
#"Added Custom" = Table.AddColumn( PriorStepName , "Team Pool Info", each if [#"School has Pool?"] = "Y" then "Y" else if [#"School has Pool?"] = "N" then "N" else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Team Pool Info"})
Thank you for your response Jakinta!
There is only one issue with this solution. I will add more details about my situation--I over-simplified my hypothetical because I worried about providing too much information, and I should have provided more details. I apologize about this!
Earlier I mentioned that there are other columns/sports with the "Pool" problem. In the table below, I've added another school, Lemon, which does not have a swim team or water polo team and so it doesn't have any pool information. Lemon does not have a pool. When I use the fill down solution however and sort the schools by school name, Lemon gets filled in with an inaccurate "Y" answer in the Pool Info column because the school before it has a pool (even though Lemon doesn't have a pool--and because Lemon doesn't have any swim team, they don't have a column with a "Y" or "N" to stop the fill down).
I also added a column for the baseball field--Lemon is the only school with a baseball team and softball team, so they're the only ones with baseball or softball information, but using the fill-down method, the other schools inaccurately have it listed that they have a baseball field when they haven't provided any data in that column:
School Player Sport School has Pool? School Has Baseball Field? Year Athlete Started on Team Year Athlete Left Team Pool Info Baseball Field Info Klein Marya Pickney Water Polo 2016 2020 Klein Raguel Dorazio Swim Y 2019 2020 Y Klein Kimberely Diblasi Swim Y 2014 2018 Y Klein Myriam Ovitt Water Polo 2011 2012 Y Klein Tula Mcpeak Swim Y 2002 2003 Y Lemon John Boyland Baseball Y 2006 2010 Y Y Lemon Tonya Yeh Softball 2018 2019 Y Y Lemon Vivan Arant Baseball Y 2015 2017 Y Y Lemon Shantae Birdsell Softball 2004 2007 Y Y Lemon Jc Rigdon Baseball Y 2018 2020 Y Y Lemon Avery Stagg Softball 2004 2005 Y Y Lemon Stephan Imai Baseball Y 2014 2016 Y Y Lemon Anastacia Callen Softball 2021 2023 Y Y Lemon Pamella Mandelbaum Baseball Y 2004 2006 Y Y Lemon Shanti Bartle Softball 2004 2005 Y Y Lemon Prudence Black Softball 2011 2014 Y Y Lemon Dorotha Boomer Softball 2018 2020 Y Y Lemon Jacqulyn Creason Softball 2004 2007 Y Y Spring Donnetta Coppedge Swim N 2016 2019 N Y Spring Waltraud Gamblin Water Polo 2002 2005 N Y Spring Bok Mcpeek Swim N 2016 2020 N Y Spring Jerilyn Chavers Water Polo 2012 2015 N Y Westfield Sherril Tackett Swim Y 2008 2011 Y Y Westfield Maxima Converse Water Polo 2007 2008 Y Y Westfield Thea Hartl Swim Y 2009 2010 Y Y Westfield Wilda Verdi Water Polo 2003 2006 Y Y Westfield Lazaro Felch Swim Y 2012 2013 Y Y Westfield Christi Jamerson Water Polo 2007 2011 Y Y Thinking on it, maybe I could create a helper conditional column that identifies if the school in the previous row is the same as the school in the current row, AND if the activity is Water Polo, and in that case it will fill in a Y or N as appropriate. The helper column could also look at if the NEXT column has the same school AND a "Swim" activity, to avoid scenarios like the above where Water Polo is listed first and it stays blank because it didn't have a Swim row to fill it in.
(As a note, I'm going to work on making a formula to see if I can figure this out--Thank you again Jakinta for your response!)
- Jakinta
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZRBb9pAEIX/yohzDsZAQo6BqG1IaFFAQSjKYbAn9oj1Ll2vaZ1f37WNgw22ox7Q4MO3b+fNm3197T0KYtm76s1RpwgL9naSUvu9RkMaFkoo+wHHn+v0r/PiOr23qxP8jEFCAu6Vxg/OgOUfjmzZnLjbJu6Roy1pEinc81ZgzI3osCjjGjpPNWMEvw5sTMd1+0Vxa+wqEQhzb0+4axJ03KIMcuiJIpVBMxVKmKhUoPTt5wRj2qIQR2pTIIU7fadGrpS01m4ozMTUu6lgULZ29KiKvfABJdxplKZNrz8qyk0NXIYWQYIJaz+mHGmUdQpjnTo98+CZAz//3yw6ro6yxO4OpFNYGgyCr/RG9dsa2tsLw0OE3Co5LMNXk5QYG/QYYWoBkm26bhECtz7PBUbWG5sDO08SW0yi1qkOy+FeuMwwQW0E/V/LC534JD07IYHerjUV/bL3Kmt3TJkQbRJVRPqLRJ3NaIbe70SkEqaaMFathtWDsdxrlkEuLSUZY+1W+z35AZ2W5+f5A3HM8ie7RmE0Jj58x2gr8i1sWdly+0b1AyZql68s7TpUj/1+QjPSnPcboo1n3PFOuOU+ZfyaYvPOJPx8zKTtIbCyk6L8qbl8L8blvM7hOf7lKPNLZvLU0fRNedL5EauQEH5kIWuUvq0+OFVuzcJHeCHtc4fsoJrsKv6EH6gVfCPhhU3CpWGDC3Iaao7tYszsfukiZN1NZ769/QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [School = _t, Player = _t, Sport = _t, #"School has Pool?" = _t, #"School Has Baseball Field?" = _t, #"Year Athlete Started on Team" = _t, #"Year Athlete Left Team" = _t]), #"Grouped Rows" = Table.Group(Source, {"School"}, {{"Gr", each _, type table }, {"Pool Info", each List.Max([#"School has Pool?"]), type nullable text}, {"Baseball Fielad Info", each List.Max([#"School Has Baseball Field?"]), type nullable text}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"School"}), #"Expanded Gr" = Table.ExpandTableColumn(#"Removed Columns", "Gr", Table.ColumnNames ( #"Removed Columns"[Gr]{0})) in #"Expanded Gr"This also worked! What would you recommend for someone who wanted to customize this code to a different data table?