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?
- Jakinta
I would recommend to that person to adjust the code accordingly. 🙂
Noone can tell more without a sample.