Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ...
Need Help
  • Jakinta's avatar
    Jakinta
    5 years ago
    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"
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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?