Forum Discussion
M formula that references other rows in same query
I made a DAX calculated column version of this formula that uses LOOKUPVALUE(), but I'm trying to figure out a way to create it in M.
This is a simplified version of a data table that I have. My data has nothing to do with sports but I wanted to make up a fake problem that would be similar to my problem. These three fake schools all have both a swim team and a water polo team, but not every school has it's own pool on-campus.
| School | Player | Sport | School has Pool? | Year Athlete Started on Team | Year Athlete Left Team |
| Westfield | Sherril Tackett | Swim | Y | 2008 | 2011 |
| Westfield | Maxima Converse | Water Polo | 2007 | 2008 | |
| Spring | Donnetta Coppedge | Swim | N | 2016 | 2019 |
| Spring | Waltraud Gamblin | Water Polo | 2002 | 2005 | |
| Klein | Marya Pickney | Swim | Y | 2016 | 2020 |
| Klein | Raguel Dorazio | Water Polo | 2019 | 2020 | |
| Westfield | Thea Hartl | Swim | Y | 2009 | 2010 |
| Westfield | Wilda Verdi | Water Polo | 2003 | 2006 | |
| Spring | Bok Mcpeek | Swim | N | 2016 | 2020 |
| Spring | Jerilyn Chavers | Water Polo | 2012 | 2015 | |
| Klein | Kimberely Diblasi | Swim | Y | 2014 | 2018 |
| Klein | Myriam Ovitt | Water Polo | 2011 | 2012 | |
| Westfield | Lazaro Felch | Swim | Y | 2012 | 2013 |
| Klein | Tula Mcpeak | Swim | Y | 2002 | 2003 |
| Westfield | Christi Jamerson | Water Polo | 2007 | 2011 |
You will notice the only rows of the data table that contain information on whether a school has a pool are in the rows where the Sport column has the string "Swim" and there is no pool data on the rows where the Sport column has "Water polo." For certain formulas we might want to know if a school has both a water polo team and it's own pool on-campus.
In M, I would like to create a new column "Pool Info" that finds the "School Has Pool" marker (which is currently only available on the "Swim" rows) and puts it on every row, like so:
| School | Player | Sport | School has Pool? | Year Athlete Started on Team | Year Athlete Left Team | Pool Info |
| Westfield | Sherril Tackett | Swim | Y | 2008 | 2011 | Y |
| Westfield | Maxima Converse | Water Polo | 2007 | 2008 | Y | |
| Spring | Donnetta Coppedge | Swim | N | 2016 | 2019 | N |
| Spring | Waltraud Gamblin | Water Polo | 2002 | 2005 | N | |
| Klein | Marya Pickney | Swim | Y | 2016 | 2020 | Y |
| Klein | Raguel Dorazio | Water Polo | 2019 | 2020 | Y | |
| Westfield | Thea Hartl | Swim | Y | 2009 | 2010 | Y |
| Westfield | Wilda Verdi | Water Polo | 2003 | 2006 | Y | |
| Spring | Bok Mcpeek | Swim | N | 2016 | 2020 | N |
| Spring | Jerilyn Chavers | Water Polo | 2012 | 2015 | N | |
| Klein | Kimberely Diblasi | Swim | Y | 2014 | 2018 | Y |
| Klein | Myriam Ovitt | Water Polo | 2011 | 2012 | Y | |
| Westfield | Lazaro Felch | Swim | Y | 2012 | 2013 | Y |
| Klein | Tula Mcpeak | Swim | Y | 2002 | 2003 | Y |
| Westfield | Christi Jamerson | Water Polo | 2007 | 2011 | Y |
In the actual data I'm dealing with, I have other calculations that depend on the pool information, and it's helpful to have that pool information on every row (instead of just on the "Swim" rows).
I made a DAX calculated column which works fine for me, but I am wondering if there is a way to achieve this in M/power query.
Pool_Info =
LOOKUPVALUE(
'Athletics'[School has Pool?], // Return the "School has Pool?" string
'Athletics'[Sport], // Go to the column "Sport"
"Swim", // Find a row that has the string "Swim"
'Athletics'[School], // the same school on the "Swim" row should match...
'Athletics'[School], // ... the school on the current row
BLANK() // If there is no school and "Swim" match, return a blank
)
- Why filtering won't work: My fake table is small, but my real data has tens of thousands of rows, thousands of distinct items in Col A, a dozen different items in col C, and several variations on my issue with rows not having "pool" information... I also am later going to write formulas where I need to have that "Pool" information in every row.
- Merging a query with itself, then removing the nulls: This would work fine if I only had one column with the "Pool" problem--merge a table with itself and remove the rows with nulls in the new Pool column so I don't have duplicate rows with nulls.
However, the issue is that I have several columns with the equivalent of the pool problem (ie there's a "Baseball diamond" column that only has information in the "Baseball" rows but not the "Softball" rows, there's a "Football equipment" column that only has information in the "Junior Varsity Football" rows but not the "Varsity Football" rows, etc).
I was considering referencing several queries to a merged-with-itself-table, with one query per column (one query with the Baseball Diamond column that has information for both baseball and softball rows, one query with the Football Equipment column for both varsity and junior varsity football rows, etc) but I worry that this could be a huge amount of data that could become a performance issue, possibly more than my current solution of creating a DAX LOOKUPVALUE() column for each of these scenarios. What do you think?
I hope I've explained this clearly. Does anyone have pointers?
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"
8 Replies
You 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!)
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 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'm a bit unfamiliar with what technique you used for the most recent solution but I can tell it's powerful. If I wanted to learn more about it, what is it called? Is this a binary object?