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"
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!)
Solution Sagelet
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.
(Note: I have editted my original reply because I thought on what Jakinta posted and I think I understand now)
This is true! Thank you again for the solution. I can see it has a lot of potential. I'm just not sure how much more specifics I can give on my situation due to confidentiality rules.I hope this helps me with my data set! For any other newbies, I wanted to break down the code. I am still a newbie myself, so please let me know if I misunderstood anything and I can edit my reply.
This is the Source step in the query. Jakinta may have obtained this by using the "Enter Data" button in Power BI's home tab. It is okay if your source looks different than this (ie if you are getting your data from an Excel spreadsheet by clicking "Get Data" and selecting your spreadsheet):"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))"
If you want to adapt Jakinta's code to your data, you cannot copy and paste the long string of text that starts with "lZRBb9pAEIX/yohzDsZAQo6..." That long string of text, numbers and characters is a unique code that shows the computer the dummy data from this thread. Because your data will likely be different than my dummy data, you will not get the results you want if you use this string of charcters.
Currently the source step in my actual table source looks more like this, since I got my data from an Excel spreadsheet:
let
Source = Excel.Workbook(File.Contents("filepath\myexcelfile.xlsx"), null, true),
sheetname_Sheet = Source{[Item="sheetname",Kind="Sheet"]}[Data],I kept my Source step and adapted Jakinta's code after the Source step. (Please let me know if I was incorrect to do this, so that way other newbies can learn properly instead of following my example)
In this next part of the code, Jakinta groups rows together and defines the column data types in the same step.Remember you can have types such as "nullable date" or "nullable number" if that's more relevant to your particular data:
#"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}}),
Jakinta's code then removes a column--I don't quite understand this part, but you can adapt it to your code by replacing the appropriate column name.
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"School"}),This next part gives you the new columns that have the data you want on every single row:
#"Expanded Gr" = Table.ExpandTableColumn(#"Removed Columns", "Gr", Table.ColumnNames ( #"Removed Columns"[Gr]{0}))
I hope I understood what was going on correctly. Please let me know and I can correct this for other newbies like myself who read this thread.