Referencing another row in an power query if then else statement

[joooffice]

I have my data as below and am trying to find a way in my query to reference another row but the row isnt always previous or next.

I'm trying to complete the Group column where empty fields are populated by the male adult in the same account.

ie Jack Bloggs is in account 123 with his parents Joe and Angela. Joe is the male adult in the account and is in group A so I want A to appear in the group column. Joe is 2 rows above Jack. 

in Account 125 Bob should show in group B but Bob is the row above Jason (the male adult)

in account 126 there is no male adult so the child barbara's group should stay blank

I'm relatively new to Power Query but used to if then else statements, its just the referencing that i'm struggling with. I then need to use the group column to further manipulate my data which is why i want to do it in power query.

Thanks!

Account ID	Name	Member Type	Gender	Group
123	Joe Bloggs	Adult	M	A
123	Angela Bloggs	Adult	F	B
123	Jack Bloggs	Child	M
125	Bob Smith	Child	M
125	Jason Smith	Adult	M	B
126	Susan Lesser	Adult	F	C
126	Barbara Lesser	Child	F
127	Claire Pinkas	Adult	F	A
127	John Pinkas	Child	M
127	Sam Pinkas	Adult	M	B

[v-juanli-msft]

Hi @joooffice 

If any of the answers slove your problem, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[camargos88 · Accepted solution]

Hi @joooffice ,

Try this mcode:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc+9CsMgEADgVxHnLG1pO2sgg7RQyBgyXBpRiVHQ5P2roI0kpYN4J5/303X4dL7gCjPLEdVWCB8SMq56CfczxrivMiJGcA1H14RDC8fgPW2qlkqPqRpK6hp/2AG1s1rkH8PAW/NV5Vy53y3E7erBoAf3nrvdWHXBKLgBHGwwd22Krvf4rkE5jl7KTLDfkxSOWWk29WuHqFqYj6XSCv0H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account ID" = _t, Name = _t, #"Member Type" = _t, Gender = _t, Group = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if [Member Type] = "Child" then
let _accountID = [Account ID],
_group = Table.SelectRows(Source, each [Account ID] = _accountID
and [Member Type] = "Adult" and [Gender] = "M")[Group]
in
if List.Count(_group) > 0 then _group{0} else null
else null),
#"Merged Columns" = Table.CombineColumns(#"Added Custom",{"Custom", "Group"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Group.1")
in
#"Merged Columns"

[Anonymous]

try using this expression to add new/uptodate  Group column

Table.AddColumn(yourtab, "colPers", (r)=>if r[Member Type]="Child" then try Table.SelectRows(Table.SelectRows(mt,each [Account ID]=r[Account ID]),each [Member Type]="Adult" and [Gender]="M"){0}[Group] otherwise null else r[Group])

[Anonymous]

This expression transforms the column [Group] in place or, to be more precise, transform the [Group] field of the rows of the table, without the need to create a temporary one to be used to replace the old one

= Table.FromRecords(Table.TransformRows(youTab,  (r)=>if r[Member Type]="Child" then r&  [Group=try Table.SelectRows(Table.SelectRows(youTab,each [Account ID]=r[Account ID]),each [Member Type]="Adult" and [Gender]="M"){0}[Group] otherwise r[Group]] else r))

This approach is probably not the best from a performance perspective, but ...